CODEWITHSUNDEEP
phpmysqltimezoneconvert-tz
Martin Petercak
Martin Petercak

Reputation: 833

Mysql timezone and selecting rows from one day

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?

Upvotes: 4

Views: 4248

Answers (4)

Imam_AI
Imam_AI

Reputation: 151

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')

Upvotes: 0

Rick James
Rick James

Reputation: 142346

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.

Upvotes: 5

MvG
MvG

Reputation: 60928

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.

Upvotes: 10

Brian
Brian

Reputation: 8616

  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.

Upvotes: 1

Related Questions