How to convert a timestamp to a string

Question

This is a bit weird. I need to save a timestamp into a varchar(15), the column is defined as varchar(15) and it stores the actual numeric timestamp.

So I have something like "2015-03-01 20:00:00" and it need to be saved as "1427725068". Is there any way to do so directly in mysql? Thanks

UNIX_TIMESTAMP() https://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_utc-time — Alex, Apr 09 '15 at 14:23
This is what you're probably looking for: http://stackoverflow.com/questions/11133760/mysql-convert-datetime-to-unix-timestamp — Joram, Apr 09 '15 at 14:24

score 1 · Accepted Answer · answered Apr 09 '15 at 14:21

1

The varchar dates are not real date and you need to convert them to real date before doing any further operation. The function is str_to_date and then use unix_timestamp on it to get the value as

mysql> select unix_timestamp(str_to_date('2015-03-01 20:00:00','%Y-%m-%d %H:%i:%s')) as utime;
+------------+
| utime      |
+------------+
| 1425220200 |
+------------+

answered Apr 09 '15 at 14:21

Abhik Chakraborty

44,654
6
52
63

Should work, but if you try to do insert into table (field_varchar_15) values mysql> select unix_timestamp(str_to_date('2015-03-01 20:00:00','%Y-%m-%d %H:%i:%s')) as utime; it doesn't store the real timestamp but the date converted to 2015- ..... – petekaner Apr 09 '15 at 14:28
I did not get could you explain a bit ? – Abhik Chakraborty Apr 09 '15 at 14:32
sorry, missunderstanding :) you're right, that works – petekaner Apr 09 '15 at 15:08

Alex · Answer 2 · 2015-04-09T14:45:01.993

0

Here is fiddle: http://sqlfiddle.com/#!2/dd7f40/1

create table table1 (id int, timestamp_varchar varchar(15));

insert into table1 values(1,UNIX_TIMESTAMP('2015-03-01 20:00:00'));

select * from table1;

output:

1, 1425258000

edited Apr 09 '15 at 14:45

answered Apr 09 '15 at 14:38

Alex

16,739
1
28
51

How to convert a timestamp to a string

2 Answers2