1

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.

O. Jones
  • 103,626
  • 17
  • 118
  • 172
Kshitiz Katiyar
  • 11
  • 6
  • Is there an Oracle PL/SQL component to go with that tag? – William Robertson Jun 17 '18 at 09:39
  • I add plsql as a tag because I'm thinking that there will be a use of plsql in execution as I've also tried it. – Kshitiz Katiyar Jun 17 '18 at 10:04
  • Have a look at the `from_unixtime` function. – dnoeth Jun 17 '18 at 10:06
  • Have a look at [this old SO post](https://stackoverflow.com/questions/23994889/). And remove the plsql tag, [it's an Oracle thing](https://stackoverflow.com/questions/9808876/). – LukStorms Jun 17 '18 at 10:08
  • ok but how am I convert all those epoch_time together and insert it into same table with a new column (let's say - 'date') which is having a date parallel to that epoch_time. – Kshitiz Katiyar Jun 17 '18 at 10:42
  • Can you provide some examples of the *values* of the `epoch_time` column? Your comment on my answer says it is `VARCHAR()`. What's in it? – O. Jones Jun 18 '18 at 16:05

1 Answers1

1

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;
O. Jones
  • 103,626
  • 17
  • 118
  • 172
  • I will definitely try this. Thanks – Kshitiz Katiyar Jun 17 '18 at 11:57
  • { epochtime} in my table is in varchar and when I'm applying this - {UPDATE mytable SET datestamp = FROM_UNIXTIME(epochtime);} it isn't changing the values in the datestamp column showing {null} by default. – Kshitiz Katiyar Jun 18 '18 at 06:13
  • it works I've done a little change in the query i.e. `UPDATE mytable SET datestamp = FROM_UNIXTIME(epochtime\1000000); ` and it works perfectly. – Kshitiz Katiyar Jun 19 '18 at 06:38
  • This query `UPDATE mytable SET datestamp = FROM_UNIXTIME(epochtime\1000000);` is giving the time in `GMT+05:30` but I want that it in `GMT` then what changes should I have to do in the query. – Kshitiz Katiyar Jun 27 '18 at 08:56