How to get #entries of last hour in MySQL, correcting for timezone?

Posted by Ferdy on Stack Overflow See other posts from Stack Overflow or by Ferdy
Published on 2010-04-16T15:29:59Z Indexed on 2010/04/16 15:33 UTC
Read the original article Hit count: 271

Filed under:
|
|

I am storing activity entries in a MySQL table. The table has a date_created field of type timestamp and through PHP I insert the activity entries based on GMT:

$timestamp = gmdate("Y-m-d H:i:s", time());

This works fine. On my client I am on GMT+2. If it is 16:00 here and I insert an entry, it is stored in MySQL as 14:00. This is as expected I guess. My

Now I would like to get the number of activity entries from MySQL within the last hour. I'm using the following query:

SELECT COUNT(id) as cnt FROM karmalog WHERE user_id = ' 89' AND event='IMG_UPD' 
AND date_created > DATE_SUB(CURRENT_TIMESTAMP, INTERVAL 1 HOUR)

This returns nothing, because CURRENT_TIMESTAMP uses the MySQL timezone settings, which is set to SYSTEM, which is set to GMT+2. I guess what I am looking for is a GMT_CURRENTTIMESTAMP in MySQL, is there such a thing?

© Stack Overflow or respective owner

Related posts about mysql

Related posts about timezone