现在()没有时区

我有一个类型为 timestamp without time zone的 added_at列。我希望它的默认值是当前的日期时间，但没有时区。函数 now()也返回一个时区。

我该怎么解决这个问题？

135742 次浏览
小开

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.

小开
最佳答案 
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');
小开

"Current Date/Time":

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

小开

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.


提交答案