Handle a different timezone in mysql

Question

Long story short, I'm working on a server that has a different time zone to my local area, although the output should be relevant to this local area alone. I wrote a query to see if some record had been stored for 3 or more hours, and that works a treat, however, I'm trying to find a way to re-write it so that it can convert the time to this time zone, prior to comparing.

With my most recent attempt, it looks like it compares the time prior to converting the time, I was just wondering if there’s a way of doing it through mysql, I should probably also mention that I’m currently limited to using version 5.096. Below you can see what I’ve written so far, I only noticed there was a timezone issue once I actually uploaded a file onto my FTP and tested it through that.

If I’m doing something stupidly wrong, please tell me what I’m doing wrong exactly, and if you could provide a solution, it would be much appreciated! Thanks!

SELECT * FROM 
(
    SELECT *, IF(HOUR(TIMEDIFF(CONVERT_TZ(current_time, '-07:00', '+00:00'), Time)) >= 3, 1, 0) = 1 AS timing
    FROM callLogs
    WHERE Inqueue = '1'
    ORDER BY Time, Inqueue ASC
) AS qry

WHERE qry.timing = '1'

Another thing I should probably point out, I can't use any stored procedures, I'm quite limited in terms of what I can do as it turns out, which is just great! I also tried to contact the hosting company that is being used, and they came back with nothing useful, I think their English is very broken. They weren't answering a simple question.... So... Any ideas? ¯_(ツ)_/¯

Answer 1

Make sure that timezone support is enabled by mysql: mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u root -p mysql

The system is your system timezone, the below query gives time difference between your system and GMT in seconds.

SELECT TIMESTAMPDIFF(
  SECOND, TIMESTAMP('2018-02-01 00:00:00'),  
  CONVERT_TZ(TIMESTAMP('2018-02-01 00:00:00'), 'SYSTEM', 'GMT')
)