MySQL: Converting time and date to unix timestamp

Question

How can I convert strings holding some time and date in plain format, e.g. 20:21:14 - 21/01/2020 to a unix timestamp format, e.g.1579638074 in SQL?

Thanks

score 0 · Answer 1 · answered Jan 22 '20 at 08:14

0

You can use UNIX_TIMESTAMP(); function UNIX_TIMESTAMP(); UNIX_TIMESTAMP(date);

For more information please visit - https://www.w3resource.com/mysql/date-and-time-functions/mysql-unix_timestamp-function.php

answered Jan 22 '20 at 08:14

Mr Freak

216
3
20

score 0 · Answer 2 · answered Jan 22 '20 at 08:21

0

SELECT UNIX_TIMESTAMP(STR_TO_DATE('20:21:14 - 21/01/2020','%h:%i:%s - %d/%m/%Y'))n;

answered Jan 22 '20 at 08:21

Strawberry

33,750
13
40
57

How can upgrade all my table "date" ? – Cosmin Jan 23 '20 at 14:03

score 0 · Answer 3 · edited Jan 23 '20 at 15:03

0

SELECT STR_TO_DATE('2014-05-28 11:30:10','%Y-%m-%d %H:%i:%s');

SELECT STR_TO_DATE('20140528 11:30:10','%Y%m%d %H:%i:%s');

SELECT STR_TO_DATE('2014-05-28 113010','%Y-%m-%d %H%i%s') ;

SELECT STR_TO_DATE('20140528 113010','%Y%m%d %H%i%s');

I Think This Will Help You.

edited Jan 23 '20 at 15:03

Strawberry

33,750
13
40
57

answered Jan 22 '20 at 08:21

Guarav

33
1
10

score 0 · Answer 4 · answered Jan 22 '20 at 08:22

Use Unix_TIMESTAMP

UNIX_TIMESTAMP(timestring)

Time format: YYYY-MM-DD HH:MM:SS or YYMMDD or YYYYMMDD

Example:

MariaDB [testl]> create table test(time int(15));
Query OK, 0 rows affected (0.416 sec)

 MariaDB [testl]> 
MariaDB [testl]> insert into test(time) values(UNIX_TIMESTAMP('2020-01-21 20:21:14'));
Query OK, 1 row affected (0.102 sec)

MariaDB [testl]> select * from test;
+------------+
| time       |
+------------+
| 1579618274 |
+------------+
1 row in set (0.039 sec)

MySQL: Converting time and date to unix timestamp

4 Answers4