Convert mysql DATETIME column to epoch seconds

Question

I have a column mysql datetime that is in DATETIME format.

Is there a way to SELECT this column in epoch seconds? If not, what would be the best way of converting the datetime format to epoch seconds? What type of field would be the best to capture this?

score 52 · Accepted Answer · answered May 31 '12 at 18:29

52

Use MySQL's UNIX_TIMESTAMP function

answered May 31 '12 at 18:29

John Conde

217,595
99
455
496

1

Make sure your mysql server timezone is set correctly, otherwise you will get values you do not expect. EX: SET GLOBAL time_zone = UTC; and UNIX_TIMESTAMP(created) * 1000 as epoc_ms. See http://dev.mysql.com/doc/refman/5.5/en/time-zone-support.html for more info. – rynop Oct 03 '13 at 16:02

score 28 · Answer 2 · answered May 31 '12 at 18:32

28

You can use :

SELECT UNIX_TIMESTAMP(yourfield) FROM yourtable;

And for UPDATE, the invers function is : FROM_UNIXTIME(...)

answered May 31 '12 at 18:32

Akarun

3,220
2
24
25

Convert mysql DATETIME column to epoch seconds

2 Answers2

Linked

Related