1

What is the behavior of PostgreSQL when we cast a DATE to TIMESTAMP to TIMESTAMPTZ What time zone is used?

  1. PostgreSQL server
  2. Client that run the query (Current session)
WebMaster
  • 3,050
  • 4
  • 25
  • 77
  • Really. You can't do `select current_date::timestamp, current_date::timestamptz` and see? For more information see [Timestamps](https://www.postgresql.org/docs/current/datatype-datetime.html#DATATYPE-DATETIME-INPUT)(8.5.1.3. Time Stamps) – Adrian Klaver Jan 31 '21 at 16:25
  • What do you mean? Your query works without error and casted successfully – WebMaster Jan 31 '21 at 16:52
  • You wanted to know what the behavior is and that shows you. Look at the output and read the information at the link, it will answer question and more. – Adrian Klaver Jan 31 '21 at 16:56
  • I already read the doc, and the behavior is not clear, what is the time zone here? Do you know? – WebMaster Jan 31 '21 at 16:58
  • 1
    You have not provided enough information. Run `SHOW TimeZone;` Provide an example of the dates being used and the output of the casts. Indicate the client being used. – Adrian Klaver Jan 31 '21 at 17:03

3 Answers3

3

If you cast a date to a timestamp, time zones don't play a role, because both data types are without a time zone. The resulting timestamp will be the beginning of the day.

If you cast date to timestamp with time zone, resulting timestamp will be the beginning of the date in the time zone defined by the parameter timezone in your current session.

SHOW timezone;

   TimeZone    
---------------
 Europe/Vienna
(1 row)

SELECT CAST (DATE '2021-01-15' AS timestamp);

      timestamp      
---------------------
 2021-01-15 00:00:00
(1 row)

SELECT CAST (DATE '2021-01-15' AS timestamp with time zone);

      timestamptz       
------------------------
 2021-01-15 00:00:00+01
(1 row)
Laurenz Albe
  • 209,280
  • 17
  • 206
  • 263
1

Casting date to timestamp will append time 00:00:00.0 to the date. The time zone of the current server session will be used.
By default this is the time zone setting of Postgresql server.
You can change the time zone of the current server session like this:

set time zone 'Europe/Sofia';

I do not think that the time zone of the client has any effect. More on this issue here.

  • Edit

As Adrian Klaver noticed "When dealing with timestamps it's best to assume the worst". Therefore better set the session time zone explicitly.

Stefanov.sm
  • 11,215
  • 2
  • 21
  • 21
  • 1
    The client's time zone has an effect, especially when comparing `timestamp` and timestamptz` types. It determines what the initial time zone is for the passed in value. In the `timestamp` case it becomes a tz naive value that loses its time zone information. In the `timestamptz` case it gets rotated to `UTC` depending on the `TimeZone` value, which can be changed by the client. – Adrian Klaver Jan 31 '21 at 18:19
  • @AdrianKlaver I mean casting only because it seems to me that this is what the question is about. BTW are there clients that implicitly set the time zone of the connection/session? – Stefanov.sm Jan 31 '21 at 18:52
  • I have seen cases where folks had the env variable `PGTZ`[Env](https://www.postgresql.org/docs/current/libpq-envars.html) set which caused all `libpq` sessions to change the time zone. Also where folks did an explicit `SET timezone ...` in their connection set up. When dealing with timestamps it best to assume the worst. – Adrian Klaver Jan 31 '21 at 19:53
1

Given the situation where one needs to change the column type from integer (as epoc seconds) to timzezonetz -> casting directly from integer to timezonetz is not possible. but you can do this:

ALTER TABLE table_name ALTER COLUMN column_name TYPE timestamptz 
  USING column_name::abstime::timestamptz
Ken Kotch
  • 21
  • 2