I have a table containing 100’s of entries –
I have since after import, updated the columns to include a timestamp.
The data is given to me by XMl and imported into DB
I have 3 columns in the data.
A date in date
format.
A time in varchar
format,
And a timestamp.
The timestamp is created out of the date and time put together, which works fine in PHP for all new entries, but I need to update the old entries to have a timestamp as well.
Examples :
The new entries comes in like this
ANK_BD
= 2020-02-13
ANK_BT
= 0945
ANK_Timestamp
= 2020-02-13 09:45:00
But the old entries look like this
ANK_BD
= 2020-02-13
ANK_BT
= 0945
ANK_Timestamp
= null
Now I want to run a SQL query to update the old entries.
But I simply can not figure out how I can create the time out of the ANK_BT
Something like UPDATE 'tablename' SET 'ANK_timestamp'= 'ANK_BD'+'ANK_BT' WHERE 'ANK_timestamp' is null
Is this even possible?
2
Answers
You can concatenate the values together and use
str_to_date()
:Do not use single quotes around column names.
You can use mysql
TIMESTAMP
function:It need concat to update correct timestmp. ‘0945’ is converted to ’00:09:45′ but ‘094500’ is converted to ’09:45:00′
Hope this helps