timestamp comparing

[Destramic]

im having problem comparing timestamps in my where clause for some strange reason...my query works perfect but when adding WHERE it doesn't bring up a result although it should...I've been playing about with it for almost a day now...and it doesn't make sense

unix_timestamp values

start_date_time   1406411505 
end_date_time     1407275505
now               1406461573

my query

SELECT i.item_id, 
		         i.title, 
		         i.price,
		         @start_date_time := CONVERT_TZ(i.start_date_time, '+00:00', u.time_zone),
		         @end_date_time := DATE_ADD(@start_date_time, INTERVAL 10 DAY),
		         @end_date_time AS `end_date_time`
		  FROM items i
		  LEFT JOIN sub_categories sc ON sc.sub_category_id = i.sub_category_id
		  LEFT JOIN categories c ON c.category_id = sc.category_id
		  JOIN users u ON username = 'Destramic'
		  WHERE UNIX_TIMESTAMP(@start_date_time) < UNIX_TIMESTAMP(NOW()) 
		  AND UNIX_TIMESTAMP(@end_date_time) >= UNIX_TIMESTAMP(NOW())

if anyone can diagnose my problem I'd it would be much appreciated

 

[mac_gyver]

a) don't bother with unix timestamps. you can compare datetime values directly.

 

b) are you getting any query errors? off of the top of my head, the where clause cannot use derived values from the select term, because the where clause determines what rows are selected and you cannot produce the values in the select term until you have selected the rows.

[Destramic]

ok I've taken out the unix_timestamp()...but the query isn't producing a result

@end_date_time = 2014-08-05 22:51:45
@start_date_time = 2014-07-26 22:51:45

now obviously these vars match my WHERE's clause...which should bring a result back....

@end_date time >= now()

@start_date_time < now()

 

confused....

Edited July 27, 2014 by Destramic

[mac_gyver]

re-read this -

off of the top of my head, the where clause cannot use derived values from the select term, because the where clause determines what rows are selected and you cannot produce the values in the select term until you have selected the rows.

 

 

without the WHERE clause, you are SEEING the correct datetime values in the SELECT term, because the rows have been unconditionally matched by the query, but when the query with the where clause in it runs, the user variables don't have those values in them and the WHERE clause is false. (edit: when i tested this, there were no query errors, likely because of null values being used for the user variables. had you used alias names for the derived values and use the aliases in the where clause, you would have gotten query errors.)

 

you either need to put the expressions using convert_tz() and date_add(repeating the convert_tz() expression here...) into the WHERE clause OR you need to change the WHERE clause into a HAVING clause.

 

also, your JOIN users u ON username = 'Destramic' isn't complete. you need to relate the users table to the other table(s) in the query, something like JOIN users u ON u.user_id = i.user_id AND u.username='Destramic' (whatever condition ties the users to their data in the other tables.)

[Destramic]

SELECT i.item_id, 
		         i.title, 
		         i.price,
		         @end_date_time := DATE_ADD(i.start_date_time, INTERVAL 10 DAY),
		         CONVERT_TZ(@end_date_time, '+00:00', u.time_zone) AS `end_date_time`
		  FROM items i
		  LEFT JOIN sub_categories sc ON sc.sub_category_id = i.sub_category_id
		  LEFT JOIN categories c ON c.name = 'Video Games & Consoles'
		  JOIN users u
		  WHERE u.username = 'Destramic'
		  AND i.start_date_time < NOW() 
		  AND DATE_ADD(i.start_date_time, INTERVAL 10 DAY) >= NOW()

works like a charm...thank you