Convert a varchar timestamp field to an actual date field

Asked Jul 29 '20 at 16:26

Active Jul 29 '20 at 22:05

Viewed 24 times

I inherited a really messy, poorly-designed database, and there are certain things I can't change. The timestamp field in the database is a varchar field, which contains data that looks like this: 1595974379 1595977031 1596033136 It's not even a proper date format.

When I output the data on screen, I can tweak it through code to display the actual date:
$transdate = date("Y.m.d",$getLedger->getColumnVal("timestamp"));

However, is there a way to convert ALL of the dates in the database to an actual date instead of having to deal with a date being a varchar field? Is there a way to do this without losing all of the data? I created a new field, called transdate, which IS a date field.

I tried updating that field with data from the timestamp (varchar) field, but it's not working.

UPDATE transactions SET transdate = STR_TO_DATE(timestamp, '%Y-%m-%d')

I get an error message that says: Incorrect datetime value: '1595974379' for function str_to_date

I would like to take the data from the timestamp field, convert it to an actual date, and move that date into the transdate field.

Thank you!

edited Jul 29 '20 at 22:05

Dharman

30,962
25
85
135

asked Jul 29 '20 at 16:26

Blondie

The function you want is `FROM_UNIXTIME(timestamp)` – Barmar Jul 29 '20 at 16:30
So it would be UPDATE transactions SET transdate = FROM_UNIXTIME(timestamp,'%Y.%m.%d') – Blondie Jul 30 '20 at 00:25
No. Have you looked at the documentation. Why would `FROM_UNIXTIME()` need a format string. The argument is a numeric timestamp, the result is a `DATETIME`. There's no formatting. – Barmar Jul 30 '20 at 00:35
Or read the linked question. – Barmar Jul 30 '20 at 00:35
`SET transdate = FROM_UNIXTIME(timestamp)` – Barmar Jul 30 '20 at 00:36
Barmar, thank you! It worked but I can't select your comment as the answer... – Blondie Jul 30 '20 at 20:50
I didn't post an answer that can be accepted, since it's already answered in the duplicate question. – Barmar Jul 30 '20 at 20:51
Barmar, thank you for your help. I didn't see the previous post, obviously. I appreciate your help! – Blondie Jul 31 '20 at 02:32

Convert a varchar timestamp field to an actual date field

0 Answers0