How to Add a Variable Number of Hours to a Date in Postgresql

PGSQL - How do I Add 5 hours to DATE in WHERE Clause?

Instead of manually adding interval to get wanted time zone, use at time zone, eg:

t=# select now(), now() at time zone 'est';
             now              |         timezone
------------------------------+---------------------------
 2017-04-07 07:07:39.17234+00 | 2017-04-07 02:07:39.17234
(1 row)

Depending on your timezone, exactly same statement adding interval to your date gives different result, eg at DST shift hour:

t=# set timezone TO 'WET';
SET
t=# select '2017-03-26 00:00:00'::timestamptz + '1 hour'::interval;
        ?column?
------------------------
 2017-03-26 02:00:00+01
(1 row)

t=# set timezone TO 'EET';
SET
t=# select '2017-03-26 00:00:00'::timestamptz + '1 hour'::interval;
        ?column?
------------------------
 2017-03-26 01:00:00+02
(1 row)

Postgresql: add 24 hours on a timestamp

+00 meant it is timestamp with timezone and your client timezone is UTC.

If you dont want those +00 on the screen, cast it to timestamp without timezone, eg:

t=# select now();
              now
-------------------------------
 2017-05-23 09:04:46.105322+00
(1 row)

Time: 0.690 ms
t=# select now()::timestamp;
            now
----------------------------
 2017-05-23 09:04:51.849522
(1 row)

Time: 0.537 ms

So for query in original post it would be:

select (to_timestamp(timestamp_start, 'YYYY-MM-DD HH24:MI:SS.US') + interval '24 hour')::timestamp as tstamp 
from tablename

How to add number of days in postgresql datetime

This will give you the deadline :

select id,  
       title,
       created_at + interval '1' day * claim_window as deadline
from projects

Alternatively the function make_interval can be used:

select id,  
       title,
       created_at + make_interval(days => claim_window) as deadline
from projects

To get all projects where the deadline is over, use:

select *
from (
  select id, 
         created_at + interval '1' day * claim_window as deadline
  from projects
) t
where localtimestamp at time zone 'UTC' > deadline

Using a variable period in an interval in Postgres

Use this line:

startDate TIMESTAMP := endDate - ($3 || ' MONTH')::INTERVAL;

and note the space before MONTH.
Basically: You construct a string with like 4 MONTH and cast it with ::type into a proper interval.

Edit: I' have found another solution: You can calculate with interval like this:

startDate TIMESTAMP := endDate - $3 * INTERVAL '1 MONTH';

This looks a little bit nicer to me.

PostgreSQL add constant time to date function: now()::date

You can simply add a time to a date

between current_date + time '19:00:00' and current_date + time '19:34:59'

Or if the upper limit should be "tomorrow" just add one day to current_date

between current_date + time '19:00:00' and (current_date + 1) + time '19:34:59'

Concatenate date(timestamp) and hour(numeric) column in one column in psql

you can convert the hours to an interval using make_interval() and add that to the timestamp:

If hour is not an integer, you need to cast it.

select t.tx_date + make_interval(hours => t.hour::int)
from the_table t;

If hour is a numeric value with decimals (and those decimals) should be honored as fractional hours, you can convert to seconds.

select t.tx_date + make_interval(secs => t.hour * 60 * 60)
from the_table t;

(and if hour doesn't contain fractional hours, then why is it a numeric, and not an integer)

Online example: https://rextester.com/RHYA54311

How can I update the time in a column without affecting the date?

We can try truncating all timestamps to midnight, then adding 9 hours:

UPDATE note
SET entered = DATE_TRUNC('day', entered) + interval '9' hour;

---

Related Topics