Mysql double to datetime

Question

I have a row of dates that are stored as double 778867200000 and I now need to convert them to datetime. I don't want to try to convert them in the same row just in case it goes wrong. Therefore I've added a new row and default is NULL.

I've tried a few combinations to achieve what I'm looking for but with no luck.

Here's the type of thing I've been trying...

 UPDATE daily_quotes t, (SELECT DISTINCT id, date
                    FROM daily_quotes
                   WHERE id BETWEEN 1 AND 5) t1
SET t.unix_date = CONVERT(t1.date, DATETIME)
WHERE t.id = t1.id

Can anybody offer a solution?

Thanks

Steve

PS: I'm just restricting it to 1-5 for testing purposes. I actually have over 300k records.

Answer 1

Assuming your double is supposed to be in Unix epoch time, you'll want to use the MySQL function FROM_UNIXTIME().

SET t.unix_date = FROM_UNIXTIME(t1.date)

Since your timestamps have three more digits than they should, you can trim them with substring or divide by 1000 as pointed out by Schwern.

SUBSTRING(t1.date, 1, length(t1.date) - 3)

is equivalent to:

FLOOR(t1.date/1000)

You could also:

ROUND(t1.date/1000)

Rounding may be necessary if FROM_UNIXTIME doesn't support fractions.

So all together:

SET t.unix_date = FROM_UNIXTIME(SUBSTRING(t1.date, 1, length(t1.date) - 3))