4

I use MySQL DATETIME column to store date & time. Dates are in UTC. I want to select item from one day. What i'm doing now:

SELECT * FROM data WHERE DATE(CONVERT_TZ(datetime, 'UTC', 'Australia/Sydney')) = '2012-06-01'

note that the timezone depends on user

Problem is that it is quite slow with table growing. Is there any solution how to make it faster?

fedorqui
  • 275,237
  • 103
  • 548
  • 598
Martin Petercak
  • 833
  • 1
  • 9
  • 11
  • 1
    Why don't you get the timezone before the query and treat the date yourself with php and a timezone converter ? – Jerska Aug 01 '12 at 08:17

4 Answers4

10

Currently your query has to compute the conversion for every row of the database. You probably could make things better by converting the other way round, so that the conversion only occurs once (or actually twice, as you'll have to form a range). Then a proper index on datetime should make things pretty fast.

SELECT * FROM data
WHERE datetime BETWEEN CONVERT_TZ('2012-06-01 00:00:00', 'Australia/Sydney', 'UTC')
                   AND CONVERT_TZ('2012-06-01 23:59:59', 'Australia/Sydney', 'UTC')

Or if you worry about a 23:60:00 leap second not getting matched by any query, you can do

SELECT * FROM data
WHERE datetime >= CONVERT_TZ('2012-06-01', 'Australia/Sydney', 'UTC')
  AND datetime < CONVERT_TZ('2012-06-01' + INTERVAL 1 DAY, 'Australia/Sydney', 'UTC')

In the latter form, you wouldn't have to add the hours PHP-side but instead could simply pass the date as a string parameter.

MvG
  • 57,380
  • 22
  • 148
  • 276
5

Depending on your real goal, using TIMESTAMP instead of DATETIME may be a good solution.

TIMESTAMP stores the datetime as UTC, converting as it stores and as it is fetched, from/to the local timezone. This way, what I read from your table is automatically different than what you stored (assuming we are in different timezones).

Yes, use @MvG's approach of flipping the query. Yes, use his second form.

And index the column. In composite indexes, put the timestamp last, even though it is more selective.

Rick James
  • 135,179
  • 13
  • 127
  • 222
  • Could you please help me answer this question? https://stackoverflow.com/questions/51000972/website-with-multiple-timezones-issue – Thanh Nguyen Jun 23 '18 at 16:25
1
  1. DO NOT do SELECT *
  2. Indexing - make sure apropriate colunms/id fields are indexed.
  3. Do time-conversion php-side.
  4. OR make sure you do 1 & 2 and it may be wrapped into a Stored Proc, passing timezone as param.
Brian
  • 8,418
  • 2
  • 25
  • 32
  • I don't know why I was only thinking about converting it in MySQL. PHP will do that. Thanks. – Martin Petercak Aug 01 '12 at 08:22
  • Also PLEASE don't use select * :) get into habbit of specifying columns (even if it is all of them) it will make tuning queries easier! – Brian Aug 01 '12 at 08:24
0

Currently MySQL query will be as below:

SELECT * FROM data
WHERE datetime >= CONVERT_TZ('2012-06-01', '+00:00', '+10:00')
  AND datetime < CONVERT_TZ('2012-06-01' + INTERVAL 1 DAY, '+10:00', '+00:00')
Imam_AI
  • 151
  • 1
  • 7