CODEWITHSUNDEEP
mysqlsqldatabasedatetimetimestamp
Kshitiz Katiyar
Kshitiz Katiyar

Reputation: 11

convert multiple epoch time to date in sql together

I am having`a whole mysql table of approx 40,000 rows with a column named 'epoch_time' and there is epoch time in it and I want to convert that whole table's 'epoch_time' to a equivalent 'date' together in a single sql query and I'm doing this is in php_my_admin . Thanks in advance.

Upvotes: 1

Views: 1726

Answers (1)

O. Jones
O. Jones

Reputation: 108796

I guess by epochtime you mean UNIX-style timestamps, that is, number of seconds since 1970-01-01T00:00Z. If my guess is wrong, so is the rest of my answer.

First you add a new column to the table.

 ALTER TABLE mytable ADD COLUMN datestamp DATETIME AFTER epochtime;

This names the new column datestamp and puts it right after epochtime in the list of columns.

Then you update the whole table to populate the new column from the old using FROM_UNIXTIME(). Omitting the WHERE clause makes the update work on the whole table (careful!).

UPDATE mytable SET datestamp = FROM_UNIXTIME(epochtime);

Finally, if you wish you can drop the old column.

UPDATE TABLE mytable DROP COLUMN epochtime;

If I were you I'd try all this on a copy of your database to ensure it is correct before doing it on your production database.

If your epochtime values already have the TIMESTAMP data type, they are already stored internally as UTC (f/k/a GMT) times. The update operation I suggested will convert them to local time according to the settings on your server.

If your application has users in multiple time zones, you may wish to keep using the TIMESTAMP datatype: it honors time zone settings. If your epoch times are stored in an INT column, you can create your new column with the TIMESTAMP rather than DATETIME type by substituting this line for the first one in my instructions above.

 ALTER TABLE mytable ADD COLUMN datestamp TIMESTAMP AFTER epochtime;

Upvotes: 1

Related Questions