Querying dates with different timezones at db and application level

Question

There is a created_at column of datetime type in the order table. In my application I'm storing dates to the db as datetime in UTC. When I bring them back from the db I'm presenting/formating them to the user as 'America/Tijuana'. Hence user input is expected in that timezone.

I'm very new to SQL programming, in fact I'm using the framework's integrated query building methods. Anyway, for "debugging" is what I am manually trying in phpmyadmin

SELECT * FROM `order` WHERE date(`created_at`) LIKE '%somedate%'

It works, but is comparing against UTC datetimes in the db. I want it to compare to 'America/Tijuana'.

I'm trying

SELECT * FROM `order` 
  WHERE date(convert_tz(`created_at`, 'UTC', 'America/tijuana')) LIKE '%30%'

which runs, but return 0 values. Am I doing it correctly?

Answer 1

Check this:

SELECT created_at,
convert_tz(created_at , '+00:00', '-07:00'),
convert_tz(created_at , 'UTC', 'America/tijuana')
FROM 
    (SELECT TIMESTAMP '2016-06-01 09:26:50' as created_at FROM DUAL) orders;

If the last column is null , see prerequisites at https://mariadb.com/kb/en/mariadb/convert_tz/