Transfer date row to timestamp row concating old date value with time string SQL

Question

I want to alter a table in sql by changing a row of the type date to a row of the type timestamp. I want to use the old values of type date and copy them to the new row of type timestamp. Furthermore I want to concat the timestamps with the time "12:00" (noon).

My try:

-- Create a temporary TIMESTAMP column
ALTER TABLE guest_group ADD COLUMN create_time_holder TIMESTAMP without time zone NULL;

-- Copy casted value over to the temporary column and set time to 12:00
UPDATE guest_group SET create_time_holder = arrival_date::TIMESTAMP without time zone AS "12:00";

The result should be a value (timestamp) containing the old date with the string "12:00"

2019-12-20 12:00

It's not clear whether you want 12 noon or 12 midnight. If you want midnight, then setting the time to 12:00 is incorrect. ISO timestamp standards use the 24 hour clock, meaning 00:00 is midnight. — jpmc26
– jpmc26, Commented Jun 9, 2019 at 0:39

Gordon Linoff · Accepted Answer · 2019-06-08 14:28:56Z

1

If you are using Postgres, I think this does what you want:

UPDATE guest_group
    SET create_time_holder = arrival_date::date + interval '12 hour';

answered Jun 8, 2019 at 14:28

Gordon Linoff

1.3m62 gold badges712 silver badges859 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Transfer date row to timestamp row concating old date value with time string SQL

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related