CODEWITHSUNDEEP
mysqldatetimetimezonetimezone-offset
eager_learner313
eager_learner313

Reputation: 97

mySQL need timezone displayed in EST but cannot change global time settings

I am facing an odd issue. The global timezone setting in the mySQL is in UTC. There are multiple tables (sub databases) within a single instance of mySQL (I am using database.NET as the manager); so I can't change the global timezone.

The following is my query. All I need is to display the times in EST. I have seen a few solutions with @@sessionTimeZone but they didn't work. Furthermore, I am running into the issue of data being collected till 9PM EST, but in UTC that's 1AM on the next day.

My main confusion is that the data is already stored in the table in UTC; however, I would like to see those datetime fields displayed in EST.

cartId is a numerical field createDate is a datetime field

I am just using http://fishcodelib.com/database.htm and connecting to the mySQL database.

SELECT   DATE(createDate)
    ,DATE_FORMAT(createDate, '%l%p') as HourOfDay
    ,count(cartId) as numCarts_ALL

FROM carts
WHERE createDate >= '2014-09-24' 
      AND createDate < '2014-10-01'
      AND HOUR(createDate) >= 10 AND HOUR(createDate) <21   
GROUP by DATE(createDate),HOUR(createDate)
;

I will really appreciate any help. Thanks again

Upvotes: 0

Views: 5577

Answers (3)

NormalPerson
NormalPerson

Reputation: 1

fix mysqld.cnf

sudo vim /etc/mysql/mysql.conf.d/mysqld.cnf

then add the bottom of the document

default-time-zone="UTC+N" (In my case, live in Korea: default-time-zone="+9:00")

then restart

Upvotes: 0

O. Jones
O. Jones

Reputation: 108806

DATETIME data types are not affected by either the global or connection-local timezone setting. That only works for TIMESTAMP data types. That explains why your attempts to muck around with @@session.TimeZone don't have any effect. NOW() and CURDATE() are affected, though.

You say your DATETIME data is stored in UTC. That's excellent. Life is much easier when you store your data that way.

Before you use the advice I'm about to give you, please make sure your MySQL server has its timezone tables loaded correctly. Do this command and make sure you don't get a NULL result.

 SELECT CONVERT_TZ(NOW(), 'America/New_York', 'UTC')

If this doesn't work -- if you get a NULL or an error -- you need to get your server people to load the time zone tables. They should do this. They know how. (If they don't, you should get a new service provider.)

We need to use the timezone named 'America/New_York' because presumably you want to switch automatically between EDT and EST on the appropriate dates each year.

Now, to retrieve a correctly converted UTC DATETIME value from a table, you do this:

SELECT CONVERT_TZ(createDate, 'UTC', 'America/New_York') AS createDate
  FROM yourTable

This is cool because you can make the timezone setting a user preference if you have users in various time zones.

To store a local time value as UTC just do it in reverse. For example.

INSERT INTO yourTable (createDate) VALUES (CONVERT_TZ(?, 'America/New_York', 'UTC'))

Now do 

SELECT NOW()

and look to see whether NOW() is in local time or in UTC. If it is in UTC, then you should start your session by doing SET TIME_ZONE='America/New_York' . This will get your timezone set right so NOW() and CURDATE() do what you want.

Then, to fetch yesterday's (local time) rows from your table do this:

WHERE createDate >= CONVERT_TZ(CURDATE(),'America/New_York','UTC') - INTERVAL 1 DAY
  AND createDate <  CONVERT_TZ(CURDATE(),'America/New_York','UTC')

This will convert midnight local time to UTC and fetch the range of items.

To get all the data from 4pm today until 2am tomorrow, you could do this:

4pm today (localtime) is CURDATE() + INTERVAL 16 HOUR . 2am tomorrow is CURDATE() + INTERVAL 26 HOUR or you could write it CURDATE() + INTERVAL 1 DAY + INTERVAL 2 HOUR

So fetching that range would need this:

WHERE createDate >= CONVERT_TZ(CURDATE() + INTERVAL 16 HOUR,'America/New_York','UTC')
  AND createDate <  CONVERT_TZ(CURDATE() + INTERVAL 26 HOUR,'America/New_York','UTC')

You could also use ADDTIME(CURDATE(),'16:00') to obtain a DATETIME value for 4pm today, if you'd rather combine ordinary date objects and ordinary time objects. Similarly, you could get 2am tomorrow like this:

  ADDTIME(CURDATE(),'02:00') + INTERVAL 1 DAY

Notice that this form of WHERE clause allows a range scan on an index on the createDate column. This is very good for performance.

Upvotes: 2

Gervs
Gervs

Reputation: 1397

To convert datetime from a timezone to another timezone:

SELECT DATE(CONVERT_TZ(createDate, '+00:00', '-04:00')) createdDate,
       HOUR(CONVERT_TZ(createDate, '+00:00', '-04:00')) hourOfDay
FROM carts
WHERE
    createDate BETWEEN CURRENT_DATE + INTERVAL 16 HOUR AND CURRENT_DATE + INTERVAL 26 HOUR
GROUP BY createdDate, hourOfDay

For different dates

SELECT DATE(CONVERT_TZ(createDate, '+00:00', '-04:00')) createdDate,
       HOUR(CONVERT_TZ(createDate, '+00:00', '-04:00')) hourOfDay
FROM carts
WHERE
    createDate BETWEEN '2014-09-24' AND '2014-10-01'
GROUP BY createdDate, hourOfDay
    HAVING hourOfDay <= 2 OR hourOfDay >= 20

You might think "why not in WHERE clause?"

If functions are used in the where clause, indexes can't be used (which results in a full table scan). So just filter out the records within the daterange, and the hours in the HAVING clause

FYI

CURRENT_DATE + INTERVAL 16 HOUR

Is short for

DATE_ADD(CURRENT_DATE, INTERVAL 16 HOUR)

Upvotes: 0

Related Questions