CODEWITHSUNDEEP
mysqldate
developer
developer

Reputation: 2050

How to convert human date to unix timestamp in Mysql?

I have a table with a date field, having human date in it like: '2008-01-08 19:23:32' Now i have to copy this field plus some other fields of the same table to another table, but date needs to be in unix timestamp.

Is there any function in mysql which converts human date to unix timestamp inside query itself?

Upvotes: 20

Views: 46155

Answers (5)

Diego Favero
Diego Favero

Reputation: 2145

Query:

SELECT UNIX_TIMESTAMP(TIMESTAMP(`opened`)) as timestamp_date, `opened` as datetime_type FROM `myterminal`

Outputs:

| timestamp_date        | datetime_type     
|-------------------    |---------------------
| 1536602012            | 2018-09-10 14:53:32
| 1536603854            | 2018-09-10 15:24:14

Upvotes: 0

gview
gview

Reputation: 15411

Yes. SELECT UNIX_TIMESTAMP(column) FROM TABLE

Upvotes: 0

xdazz
xdazz

Reputation: 160963

SELECT UNIX_TIMESTAMP('2007-11-30 10:30:19');

Upvotes: 4

jcomeau_ictx
jcomeau_ictx

Reputation: 38492

mysql> select unix_timestamp('2008-01-08 19:23:32');
+---------------------------------------+
| unix_timestamp('2008-01-08 19:23:32') |
+---------------------------------------+
|                            1199849012 |
+---------------------------------------+
1 row in set (0.04 sec)

found here: http://www.epochconverter.com/

Upvotes: 50

user529649
user529649

Reputation:

UNIX_TIMESTAMP() Should do the trick!

From MySQL Docs:

If called with no argument, returns a Unix timestamp (seconds since '1970-01-01 00:00:00' UTC) as an unsigned integer. If UNIX_TIMESTAMP() is called with a date argument, it returns the value of the argument as seconds since '1970-01-01 00:00:00' UTC. date may be a DATE string, a DATETIME string, a TIMESTAMP, or a number in the format YYMMDD or YYYYMMDD. The server interprets date as a value in the current time zone and converts it to an internal value in UTC.

mysql> SELECT UNIX_TIMESTAMP();
        -> 1196440210
mysql> SELECT UNIX_TIMESTAMP('2007-11-30 10:30:19');
        -> 1196440219

Upvotes: 4

Related Questions