phpmyadmin Question posted in
The official documentation can be found here.

Create a timestamp from variables in SQL and format string to time – Phpmyadmin

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?

Tags:

2

Answers


  1. 0

    You can concatenate the values together and use str_to_date():

    UPDATE tablename
    SET ANK_timestamp = str_to_date(concat(ANK_BD, ANK_BT), '%Y-%m-%d %h%i')
    WHERE ANK_timestamp is null;

    Do not use single quotes around column names.

    Login or Signup to reply.
  2. 0

    You can use mysql TIMESTAMP function: 

    UPDATE 'tablename' SET 'ANK_timestamp' = TIMESTAMP(ANK_BD, CONCAT(ANK_BT, '00'))
WHERE 'ANK_timestamp' is null

    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

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search