Question 113892 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – Update only the time from a CURRENT_TIMESTAMP

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.

Tags:

2

Answers


  1. 0

    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 ...
    Login or Signup to reply.
  2. 0

    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.

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

Back To Top
Search