1

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?

9
  • @horse hi, mariadb
    – user96062
    Jun 3, 2016 at 8:26
  • You are incorrectly applying the LIKE predicate, intended to compare strings, do a datetime value.
    – mustaccio
    Jun 3, 2016 at 15:18
  • @mustaccio it is possible to apply LIKE to dates, they just get converted to the string representation. It is not very effective but it should work.
    – jkavalik
    Jun 3, 2016 at 18:24
  • 1
    @jkavalik thank you I'm aware of implicit casting. It could work only if you guarantee the default date format remains constant. Try changing LC_TIME.
    – mustaccio
    Jun 3, 2016 at 19:45
  • 1
    Keep in mind that any function run against a column in the WHERE clause is going to produce a non-sargable query. It's better to convert the right-hand side to UTC, then use that to query the column directly. Think about it - otherwise you literally have to convert every column in your table. The more rows you have in the table, the slower the query will be. Jun 4, 2016 at 1:42

1 Answer 1

0

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/

2
  • you were correct. I added the timezone tables, thanks.
    – user96062
    Jun 3, 2016 at 17:17
  • @user1236524, you may mark it as an answer.
    – Serg
    Jun 3, 2016 at 18:14

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.