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

Question

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?

Answer 1

There is the UTC_TIMESTAMP() function, which returns the current UTC timestamp in a contextually-sensitive manner. You can use that in place of CURRENT_TIMESTAMP:

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