Update only the time from a CURRENT

How can I update and change only the time-section of a CURRENT_TIMESTAMP in postgres SQL?

I have to INSERT INTO a TABLE a new VALUE with the CURRENT_TIMESTAMP to get the correct year, month and day. The Time needs always to be 10 PM.

I tried to find a function where I eventually just get a TIMESTAMP with the current Year,month, day and the default time of 00:00:00. Later I tried to DATEADD 22:00:00 Into it. Doesn't seem to work.

CodePudding user response：

If the time part is the same for all rows without exception, I would not store it at all. Use a date column then turn that into a timestamp when selecting from the table:

select the_column   time '22:00:00' as the_column_adjusted
from the_table

Another alternative is to define a default value with a fixed time:

the_column timestamp default current_date   time '22:00:00'

This approach can also be used during an INSERT:

insert into the_table (the_column)
values (current_date   time '22:00:00');

To change existing rows, you can convert the timestamp to a date, then add the desired time back:

update the_table
   set the_column = the_column::date   time '22:00:00'
where ...

CodePudding user response：

You can convert current_timestamp, which is of type "timestamptz" (short for "timestamp with time zone"), to a "date", then back to "timestamp with time zone", which will make it 00:00 at current timezone used by connection, and then add 22h:

=> select current_timestamp::date::timestamptz '22h'::interval;
 2022-11-05 22:00:00 01

You can simplify this with just using current_date:

=> select current_date::timestamptz '22h'::interval;
 2022-11-05 22:00:00 01

If you want to make it independent from current connection timezone, and just use UTC date (or any other time zone you need), you can use:

=> select ((current_timestamp at time zone 'UTC')::date::timestamp '22h'::interval)::timestamptz;
 2022-11-05 22:00:00 01

But, if hour is not important, then "date" type would be better and more efficient to store than "timestamp with time zone".

And never ever use "timestamp" type (short for "timestamp without timezone") for anything.