PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – Why the date of CURRENT_TIMESTAMP is different from CURRENT_DATE in Postgres?

The current time in Shanghai is 2024-03-19 00:43

show timezone
+----------+
| TimeZone |
|----------|
| GMT      |
+----------+

SELECT CURRENT_DATE  AT TIME ZONE 'Asia/Shanghai', CURRENT_TIME  AT TIME ZONE 'Asia/Shanghai', CURRENT_TIMESTAMP AT TIME ZONE 'Asia/Shanghai';
 
+---------------------+-----------------------+---------------------------+
| timezone            | timezone              | timezone                  |
|---------------------+-----------------------+---------------------------|
| 2024-03-18 08:00:00 | 00:43:52.916370+08:00 | 2024-03-19 00:43:52.91637 |
+---------------------+-----------------------+---------------------------+
  1. Why the date of CURRENT_TIMESTAMP is different from CURRENT_DATE in Postgres?
  2. How can I get the current date in Shanghai for the result ‘2024-03-19’ if the time zone is GMT?
Tags:

2

Answers


  1. 0

    The similar sounding expressions CURRENT_DATE and CURRENT_TIMESTAMP are very different in nature.

    CURRENT_TIMESTAMP returns data type timestamptz (timestamp with time zone), and does not depend on local settings. (Only the display does.) When you apply the AT TIME ZONE construct you produce the corresponding local timestamp (timestamp without time zone) for the given time zone. Still the same point in universal time.

    CURRENT_DATE returns data type date, and depends on the current timezone setting of the session. It’s the local timestamp truncated to the date. This truncation introduces the difference, the quantity depends on the current timezone setting. When applying the AT TIME ZONE construct, date is coerced back to timestamp (materializing the difference accrued by truncating earlier – it’s now a different point in universal time!) before generating the matching timestamptz.

    And CURRENT_TIME returns the questionable type timetz (time with time zone). This function, as well as the data type are included in the SQL standard, but discouraged in Postgres (or generally, really). The concept of just time with time zone is inherently ambiguous. Think of daylight saving time or historical shifts. I never use timetz. (So also not CURRENT_TIME.)

    Related:

    If the current timestamp in Shanghai is '2024-03-19 01:29', what is the according date? I just need to get '2024-03-19'.

    Consider the demo:

    SELECT (timestamp '2024-03-19 01:29')::date -- current date of the given timestamp (we only know from context that it applies to Shanghai!)
     , (timestamp '2024-03-19 01:29' AT TIME ZONE 'Asia/Shanghai' AT TIME ZONE 'Europe/London')::date  -- date for the given point in time in London
    ,  (timestamptz '2024-03-18 18:29:00+01' AT TIME ZONE 'Asia/Shanghai')::date  -- date in Shanghai for the given point in time
     , (timestamptz '2024-03-18 18:29:00+01' AT TIME ZONE 'Europe/London')::date  -- date in London for the given point in time
    Login or Signup to reply.
  2. 0

    How can I get the current date in Shanghai for the result ‘2024-03-19’ if the time zone is GMT?

    As already established, date type doesn’t support time zones. If you cast it to timestamp which does support time zones, you’ll get midnight on that date. Switching between these is sufficient for points in time.

    But if you want true dates, you might be interested in timestamptz-based range type tstzrange. It can describe the actual period between two points in time that makes up a specific date. Equipped with that, you can see from GMT what Shanghai would consider the same date:

    with example(date_as_tsrange) as (values 
  ('[2024-03-19 00:00,
     2024-03-20 00:00)'::tsrange) )
select tsrange(lower(date_as_tsrange) at time zone 'GMT' at time zone 'Asia/Shanghai',
               upper(date_as_tsrange) at time zone 'GMT' at time zone 'Asia/Shanghai')
from example;
    ["2024-03-19 08:00:00","2024-03-20 08:00:00")

    To see if A happened on the same date as B, extracting the date part from each of these would get you the wrong answer; local dates are shifted compared to each other. Meanwhile, the tstzrange is specific and absolute, and @> operator tells you if the point is in the range: demo

    event_A event_A_timezone event_B event_B_timezone
    2024-03-19 07:59:00 GMT 2024-03-19 06:13:00 Asia/Shanghai
    2024-03-19 00:59:00 GMT 2024-03-19 22:13:00 Asia/Shanghai
    2024-03-19 06:47:00 GMT 2024-03-19 10:40:00 Asia/Shanghai 
    with test_with_dates_and_tz as (
  select *,event_a at time zone event_a_timezone as event_a_tz
          ,event_b at time zone event_b_timezone as event_b_tz
          ,tstzrange( (event_a::date  )::timestamp at time zone event_a_timezone
                     ,(event_a::date+1)::timestamp at time zone event_a_timezone) 
           as event_a_date
  from test)
select event_b_tz <@ event_a_date as "did B happen on A's perceived date", *
from test_with_dates_and_tz;
    did B happen on A’s perceived date event_a event_a_timezone event_b event_b_timezone event_a_tz event_b_tz event_a_date
    False 2024-03-19 07:59:00 GMT 2024-03-19 06:13:00 Asia/Shanghai 2024-03-19 07:59:00+00 2024-03-18 22:13:00+00 ["2024-03-19 00:00:00+00","2024-03-20 00:00:00+00")
    True 2024-03-19 00:59:00 GMT 2024-03-19 22:13:00 Asia/Shanghai 2024-03-19 00:59:00+00 2024-03-19 14:13:00+00 ["2024-03-19 00:00:00+00","2024-03-20 00:00:00+00")
    True 2024-03-19 06:47:00 GMT 2024-03-19 10:40:00 Asia/Shanghai 2024-03-19 06:47:00+00 2024-03-19 02:40:00+00 ["2024-03-19 00:00:00+00","2024-03-20 00:00:00+00")

    Note how the range excludes the upper bound with ): it spans up to the midnight of the following date, but doesn’t include it. It’s more elegant than making 23:59:59.999999 the upper bound, and including it with ].

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search