Apurv Apurv - 4 months ago 12
MySQL Question

Inserting resultset from select and dateadd function into same table

I have "test" table in my H2 database, and i am have some time stamps in that.
I am converting my "_time" column into normal date using 'DATEADD' function.

And I want result of my select query to get inserted into '_converted_time'

sql> select * from test;
| _TIME | _CONVERTED_TIME
| 1468309423961 | null
| 1468309423962 | null
| 1468308812001 | null
| 1468308815972 | null
(4 rows, 10 ms)
sql>
sql>
sql> select DATEADD('SECOND', SUBSTRING(_time,1,10), DATE '1970-01-01') from test;
DATEADD('SECOND', SUBSTRING(EVENT_TIME, 1, 10), DATE '1970-01-01')
2016-07-12 07:43:43.0
2016-07-12 07:43:43.0
2016-07-12 07:33:32.0
2016-07-12 07:33:35.0


hence after adding above result into _converted_time it should look like ::

sql> select * from test;
| _TIME | _CONVERTED_TIME
| 1468309423961 | 2016-07-12 07:43:43.0
| 1468309423962 | 2016-07-12 07:43:43.0
| 1468308812001 | 2016-07-12 07:33:32.0
| 1468308815972 | 2016-07-12 07:33:35.0
(4 rows, 10 ms)


Thanks in advance ...

Answer

try this

update test
set CONVERTED_TIME = DATEADD('SECOND', SUBSTRING(_time,1,10), DATE '1970-01-01') 
WHERE CONVERTED_TIME IS NULL;