108

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?

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
Incerteza
  • 32,326
  • 47
  • 154
  • 261
  • 4
    Current date-time where? At your desk? At the server? At your user's computer? Date-time without time zone doesn't make a lot of sense. –  Dec 28 '13 at 07:24
  • 3
    @MikeW At the environment where it's run. – Incerteza Dec 28 '13 at 07:26
  • Consider using `with timestamp`, especially if your code is run anywhere where daylight savings is used. Or save the time in UTC, not a local timezone. You can always choose what timezone you want to display the data in. – some Dec 28 '13 at 10:27
  • @some, is that different from `with timezone`? – Incerteza Dec 28 '13 at 12:48
  • @Alex Sorry, that was a typo. I meant `timestamp with time zone` or `timestamp(0) with time zone` if you don't want sub second precision. – some Dec 28 '13 at 13:00
  • 9
    You're misunderstanding `timestamp with time zone`. It doesn't carry a time zone with it at all. It is just in absolute UTC time, with conversion to/from the local time in the `TimeZone` session variable. Just use `timestamp with time zone`; it's almost always the right choice. – Craig Ringer Dec 29 '13 at 04:13

5 Answers5

118
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');
Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
  • 1
    `select now()::timestamptz;` is a better match – Dmitriy Kosolobov Jun 28 '18 at 09:29
  • 6
    @DmitriKosolobov: `now()` already returns `timestamptz`, hence `now()::timestamptz` doesn't do anything useful. – Erwin Brandstetter Jun 28 '18 at 11:26
  • Is that in any way better than `localtimestamp`? –  Sep 24 '20 at 09:22
  • `now()::timestamp` might do some casting, but it does not convert from local time zone to UTC for me. But `now() AT TIME ZONE 'UTC';` does the right thing. – ThoPaz Sep 25 '20 at 10:27
  • @a_horse_with_no_name: No, I don't think so. Not in modern versions any more. Subtle details changed over time. In older versions, `LOCALTIMESTAMP` was translated to `now()::timestamp` internally. Later it was translated to `('now'::text)::timestamp`. And since Postgres 10, `LOCALTIMESTAMP` seems to be a function in its own right. This also showed (and shows) in the default column name. Either way, the resulting value has always been exactly the same. Related: https://dba.stackexchange.com/a/63549/3684 – Erwin Brandstetter Sep 25 '20 at 16:37
  • @a_horse_with_no_name: See related fiddles (it was different, yet, in older versions not available at dbfiddle any more): https://dbfiddle.uk/?rdbms=postgres_9.5&fiddle=0362369a6de25808fe891ce5803671b8 https://dbfiddle.uk/?rdbms=postgres_9.6&fiddle=0362369a6de25808fe891ce5803671b8 https://dbfiddle.uk/?rdbms=postgres_10&fiddle=0362369a6de25808fe891ce5803671b8 https://dbfiddle.uk/?rdbms=postgres_13&fiddle=0362369a6de25808fe891ce5803671b8 – Erwin Brandstetter Sep 25 '20 at 16:38
  • 4
    @ThoPaz: `now() AT TIME ZONE 'UTC'` returns the local time for time zone UTC. `LOCALTIMESTAMP` or `now()::timestamp` return the local time for the current `timezone` setting of the session. Two different things! Only happens to return the same while your current session runs with `timezone = 'UTC'`. – Erwin Brandstetter Sep 25 '20 at 16:44
59

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.

Richard Huxton
  • 21,516
  • 3
  • 39
  • 51
  • Probably, yes. 1) Should I change the type of the column to be `timestamp with time zone`? 2) How do I know what's the current timezone of a machine where postgresql is running? – Incerteza Dec 28 '13 at 07:41
25

"Current Date/Time":

CURRENT_TIME and CURRENT_TIMESTAMP deliver values with time zone; LOCALTIME and LOCALTIMESTAMP deliver values without time zone.

Milen A. Radev
  • 60,241
  • 22
  • 105
  • 110
4

New, and Native Answer in 2020

In PostgreSQL, If you only want the current date-time by calling CURRENT_TIMESTAMP() without time zone, and fractional digits in the seconds field which come after the decimal point of the seconds field?

(Tested on PostgreSQL v12.4)

Then use this:

SELECT CURRENT_TIMESTAMP(0)::TIMESTAMP WITHOUT TIME ZONE;

If you define your column's data type as timestamp (not as timestamptz), then you can store the timestamp without time zone, in that case you don't neet to add TIMESTAMP WITHOUT TIME ZONE

Like this:

CREATE TABLE foo (created timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP(0))

In the above function, 0 is passed to get rid of the fractional digits in the seconds field.

Kazmi
  • 1,198
  • 9
  • 20
0

If your application doesn't care about timezone, you can use SELECT LOCALTIMESTAMP for it.

Ex:

SELECT LOCALTIMESTAMP
-- Result: 2023-01-30 17:43:33.628952
Alison Moura
  • 136
  • 5