How to get #entries of last hour in MySQL, correcting for timezone?
- by Ferdy
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?
