CODEWITHSUNDEEP
mysql
Rn2dy
Rn2dy

Reputation: 4190

How am able to find out time zone used by mysql?

When I run mysql query to find out created_at time of interested table:

select created_at from snapshots;

It returns:

+---------------------+
| created_at          |
+---------------------+
| 2013-11-12 23:05:49 |
| 2013-11-15 01:02:08 |
| 2013-11-20 00:05:05 |
| 2013-11-20 00:25:38 |
| 2013-11-20 00:25:38 |
| 2013-11-20 00:41:11 |
| 2013-11-20 00:41:11 |
| 2013-11-26 22:43:47 |
| 2013-12-02 20:27:22 |
| 2013-12-02 20:27:22 |
| 2013-12-02 23:16:54 |
| 2013-12-02 23:16:54 |
| 2013-12-12 00:39:17 |
| 2013-12-12 00:40:21 |
| 2013-12-12 00:42:28 |
| 2013-12-12 00:46:33 |
| 2013-12-12 01:24:26 |
| 2013-12-12 01:26:34 |
+---------------------+

How am I able to parse this time (by another program) without information of time zone each associated with? Why doesn't MySQL store zone information or why it is not presented in the string value?

Upvotes: 0

Views: 101

Answers (1)

ceejayoz
ceejayoz

Reputation: 180147

SELECT @@time_zone; will tell you what TZ the server is running in.

If it's a DATETIME field, it's stored with no timezone data. Essentially, plain text. Running NOW() will use the server's TZ.

If it's a TIMESTAMP field, it's stored as UTC under the hood but will be returned converted into the server's time zone.

Upvotes: 1

Related Questions