Now() without timezone

postgresql timestamp postgresql-9.2
アレックス picture アレックス · Dec 28, 2013 · Viewed 85.5k times · Source

I have a column added_at of type timestamp without time zone. I want it's default value to be the current date-time but without time zone. The function now() returns a timezone as well.

How do I solve that problem?

Answer

Erwin Brandstetter picture Erwin Brandstetter · Dec 28, 2013 
SELECT now()::timestamp;

The cast converts the timestamptz returned by now() to the corresponding timestamp in your time zone - defined by the timezone setting of the session. That's also how the standard SQL function LOCALTIMESTAMP is implemented in Postgres.

If you don't operate in multiple time zones, that works just fine. Else switch to timestamptz for added_at. The difference?

BTW, this does exactly the same, just more noisy and expensive:

SELECT now() AT TIME ZONE current_setting('timezone');

Related questions

Importing .csv with timestamp column (dd.mm.yyyy hh.mm.ss) using psql \copy

I'm trying to import data from a .csv file into a postgresql 9.2 database using the psql \COPY command (not the SQL COPY). The input .csv file contains a column with a timestamp in the dd.mm.yyyy hh.mm.ss …

Read More
alter column from time with time zone to timestamp

I am having trouble changing a column called end_date in a table called key_request from time with time zone to timestamp in my Postgres database . I have tried using the following code: alter table key_request alter column …

Read More
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