20

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?

user18099
  • 663
  • 1
  • 6
  • 13
developer
  • 2,042
  • 10
  • 40
  • 59
  • 2
    Just a note, if the date stored in your table is not in UTC then you may have to convert it to UTC timezone first and then call unix_timestamp(). I am not sure if `unix_timestamp()` automatically converts the datetime to UTC before calculating the timestamp. – Aziz Shaikh Oct 14 '11 at 06:37
  • Just a note.. this is a `datetime` field type, not `date` field type. There is a difference. – DevlshOne Dec 09 '16 at 12:47

5 Answers5

49
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/

jcomeau_ictx
  • 37,688
  • 6
  • 92
  • 107
4

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
4
SELECT UNIX_TIMESTAMP('2007-11-30 10:30:19');
xdazz
  • 158,678
  • 38
  • 247
  • 274
0

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
Diego Favero
  • 1,969
  • 2
  • 22
  • 32
0

Yes. SELECT UNIX_TIMESTAMP(column) FROM TABLE

gview
  • 14,876
  • 3
  • 46
  • 51