SQL - compare dates with different time zones

Question

I have a field in my MySQL database containing a date with a timezone that has the following format: 1986-07-10T00:00:00.000+02:00 or 1986-07-10T00:00:00.000Z (Z meaning zulu time, or UTC).

I am trying to compose a WHERE clause that checks if the difference between NOW (my timezone = Europe/Brussels) and the date field is more than e.g. 18 years. Is there any way I can accomplish this using only SQL?

I was reading about a function called CONVERT_TZ() but I'm not sure i'm going in the right direction.

what datatype is the mysql field? – Puggan Se Jul 17 '12 at 14:10 — Puggan Se, Jul 17 '12 at 14:10

Puggan Se · Accepted Answer · 2012-07-17T15:21:36.660

to convert the time to your timezone, you could do somthing like:

CONVERT_TZ(
   REPLACE(
      SUBSTRING(date, 1, 19), # '1986-07-10T00:00:00' .000+02:00
      'T',
      ' '
      ),
   IF(
      SUBSTRING(date, 24) = 'Z',
      '+00:00',
      SUBSTRING(date, 24) # 1986-07-10T00:00:00.000 '+02:00'
      ),
   'Europe/Brussels'
   )

Note from mysql manual
To use named time zones such as 'MET' or 'Europe/Moscow', the time zone tables must be properly set up. See Section 10.6, “MySQL Server Time Zone Support”, for instructions

Update exemple
if the table contains: SELECT * FROM so_peter;

+-------------------------------+
| tzdate                        |
+-------------------------------+
| 1986-07-10T00:00:00.000+02:00 |
| 1986-07-10T00:00:00.000Z      |
| 2012-07-18T00:00:00.000+07:00 |
+-------------------------------+

the result of this query is:

SELECT tzdate FROM so_peter 
WHERE CONVERT_TZ(REPLACE(SUBSTRING(tzdate, 1, 19), 'T', ' '), IF(SUBSTRING(tzdate, 24) = 'Z', '+00:00', SUBSTRING(tzdate, 24)), 'Europe/Brussels')
   > '1986-07-10 01:00:00';
+-------------------------------+
| tzdate                        |
+-------------------------------+
| 1986-07-10T00:00:00.000Z      |
| 2012-07-18T00:00:00.000+07:00 |
+-------------------------------+

thank you for your reply, but this doesn't seem to work. I get syntax errors. Also, how can I use this in a WHERE clause? — Peter, Jul 17 '12 at 15:09
sorry, missed the `,` just before the if, adding an exemple soon, just need to try it out in the database first :-) — Puggan Se, Jul 17 '12 at 15:15

SQL - compare dates with different time zones

1 Answers1