How to INSERT now() + INTERVAL in Postgres 9.x

sql postgresql timestamp postgresql-9.6
ajxs picture ajxs · Sep 16, 2017 · Viewed 15.3k times · Source

I'm trying to INSERT a future date into a timestamp with time zone column in a table in Postgres 9.6.2. I'd like this date to be the current transaction time plus an arbitrary interval, such as 1 hour. I understand how to do date/time arithmetic in a SELECT statement, but the same syntax of NOW() + INTERVAL '1 hour' does not seem to be valid in an INSERT statement.

Aside from accomplishing this at the application level, is it possible to accomplish this using a single INSERT statement using date/time arithmetic? If not, what would best practice be in this situation?

Answer

user2182349 picture user2182349 · Sep 16, 2017

I think you have it right - this worked for me:

insert into barcode (id,barcode,active,created,updated) 
    values (111,'23432', true, NOW() + INTERVAL '1 hour',NOW());

I am running Postgres 9.4.13.

Related questions

Extract date (yyyy/mm/dd) from a timestamp in PostgreSQL

I want to extract just the date part from a timestamp in PostgreSQL. I need it to be a postgresql DATE type so I can insert it into another table that expects a DATE value. For example, if I have 2011/05/26 09:00:00, …

Read More
CURRENT_TIMESTAMP in milliseconds

Is there any way to get milliseconds out of a timestamp in MySql or PostgreSql (or others just out of curiosity)? SELECT CURRENT_TIMESTAMP --> 2012-03-08 20:12:06.032572 Is there anything like this: SELECT CURRENT_MILLISEC --> 1331255526000 or the …

Read More
Timestamp Difference In Hours for PostgreSQL

Is there a TIMESTAMPDIFF() equivalent for PostgreSQL? I know I can subtract two timestamps to get a postgresql INTERVAL. I just want the difference between the two timestamps in in hours represented by an INT. I can do this in …

Read More