Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.
Moderator: General Moderators
ckuipers
Forum Commoner
Posts: 61 Joined: Mon Mar 24, 2003 6:10 am
Post
by ckuipers » Wed Jun 09, 2004 6:05 am
I'm trying to sum a substraction in an sql query, but I don't get it to work.
Can any one tell me if the following works at all, or what I'm doing wrong:
Code: Select all
SELECT login_id, type_id, SUM(time_stop - time_start) AS time_spent
FROM logs
WHERE country_id= $login_country_id
GROUP BY type_id, login_id
Was an error from my part, the above does work.
Etherguy
Forum Commoner
Posts: 70 Joined: Fri Nov 01, 2002 9:09 pm
Location: Long Island, New York
Post
by Etherguy » Wed Jun 09, 2004 9:07 am
With out knowing what format your time_stop and time_start are... I willl assume it is in a standard format.... try this :
Code: Select all
SELECT login_id, type_id, SUBTIME(time_stop, time_start) AS time_spent
FROM logs
WHERE country_id= $login_country_id
GROUP BY type_id, login_id
lostboy
Forum Contributor
Posts: 329 Joined: Mon Dec 30, 2002 8:12 pm
Location: toronto,canada
Post
by lostboy » Wed Jun 09, 2004 9:09 am
Assuming you created the fields as TIME formats, you can't use sum....
try
Code: Select all
SELECT login_id, type_id, Subtime(time_stop - time_start) AS time_spent
FROM logs
WHERE country_id= $login_country_id
GROUP BY type_id, login_id
Etherguy
Forum Commoner
Posts: 70 Joined: Fri Nov 01, 2002 9:09 pm
Location: Long Island, New York
Post
by Etherguy » Wed Jun 09, 2004 9:14 am
Lost,
You can not use the subtraction sign (-) in a subtime. A comma (,) would be the correct syntax!
lostboy
Forum Contributor
Posts: 329 Joined: Mon Dec 30, 2002 8:12 pm
Location: toronto,canada
Post
by lostboy » Wed Jun 09, 2004 9:29 am
damn, my bad
ckuipers
Forum Commoner
Posts: 61 Joined: Mon Mar 24, 2003 6:10 am
Post
by ckuipers » Wed Jun 09, 2004 10:08 am
Thx for that guys, but I found the problem, it was a typing error.
Either way, I always store my dates and times as unix time-stamps.