Now() without timezone

Question

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 1

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?

• Ignoring time zones altogether in Rails and PostgreSQL

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

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

Answer 2

Well you can do something like:

SELECT now() AT TIME ZONE current_setting('TimeZone'); SELECT now() AT TIME ZONE 'Europe/Paris'; SELECT now() AT TIME ZONE 'UTC'; 

Not sure how that makes any sense for a column "added_at". You almost always want an absolute timestamp (timestamp with time zone) not a floating one.

---

Edit responding to points below:

1. Yes, should use timestamp with time zone (absolute time) unless you have a good reason not to.

2. The client timezone is given by SHOW TimeZone or current_setting(...) as shown above.

Do take some time to skim the manuals - they cover all this quite well.