Converting local datetime values to UTC datetime values in SQL Server and PostgreSQL

Question

I am trying to understand the behavior of AT TIME ZONE and I get some strange behavioral differences between SQL Server and PostgreSQL in this regard.

For demonstration purposes I produced two queries, one for SQL Server and one for PostgreSQL.

The SQL Server query:

SELECT
  [LocalTime],
  [LocalTime] AT TIME ZONE 'W. Europe Standard Time' AS [LocalTimeWithOffset],
  ([LocalTime] AT TIME ZONE 'W. Europe Standard Time') AT TIME ZONE 'UTC' AS [UniversalTime]
FROM
  (VALUES
    (CAST('2020-06-20 12:34:56.789' AS DATETIME2)),
    (CAST('2020-12-20 12:34:56.789' AS DATETIME2))) AS [X] ([LocalTime])

produces this result (in SQL Server Management Studio):

LocalTime                    LocalTimeWithOffset                 UniversalTime
---------                    -------------------                 ----------------
2020-06-20 12:34:56.7890000  2020-06-20 12:34:56.7890000 +02:00  2020-06-20 10:34:56.7890000 +00:00
2020-12-20 12:34:56.7890000  2020-12-20 12:34:56.7890000 +01:00  2020-12-20 11:34:56.7890000 +00:00

which seems fine to me, since I live in the Netherlands: in the summertime, we are 2 hours ahead of UTC (due to daylight saving) and in the wintertime we are only 1 hour ahead of UTC.

BUT... the PostgreSQL query:

SELECT
  "LocalTime",
  "LocalTime" AT TIME ZONE 'WET' AS "LocalTimeWithOffset",
  ("LocalTime" AT TIME ZONE 'WET') AT TIME ZONE 'UTC' AS "UniversalTime"
FROM
  (VALUES
    (timestamp '2020-06-20 12:34:56.789'),
    (timestamp '2020-12-20 12:34:56.789')) AS "X" ("LocalTime")

produces the following result (in pgAdmin):

LocalTime                    LocalTimeWithOffset         UniversalTime
timestamp without time zone  timestamp with time zone    timestamp without time zone
---------------------------  ------------------------    ---------------------------
2020-06-20 12:34:56.789      2020-06-20 14:34:56.789+02  2020-06-20 12:34:56.789
2020-12-20 12:34:56.789      2020-12-20 13:34:56.789+01  2020-12-20 12:34:56.789

which seems incorrect to me.

After struggling with this for a little while, I came up with another PostgreSQL query, which seems to yield the same results as the SQL Server query:

SELECT
  "LocalTime",
  "LocalTime"::timestamptz AS "LocalTimeWithOffset",
  "LocalTime"::timestamptz AT TIME ZONE 'UTC' AS "UniversalTime"
FROM
  (VALUES
    (timestamp '2020-06-20 12:34:56.789'),
    (timestamp '2020-12-20 12:34:56.789')) AS "X" ("LocalTime")

But this query seems to be dependent on the PostgreSQL server's time zone. :-(

Is there any (preferably elegant) way to mimic the SQL Server logic/behavior in PostgreSQL without a dependency on the server's time zone (and use an explicitly specified time zone in the query)?

Edit

I am aware that it is a good practice to store UTC date/time values in the database and convert them (preferably in/by the client) to local date/time values when presented to end users. I am intending to do so as well, of course, but my current scenario is a database migration from SQL Server to PostgreSQL (as part of a big application overhaul), where the DATETIME2 table columns in the SQL Server database contain local date/time values, which I want to convert to UTC date/time values in corresponding timestamp columns in the PostgreSQL tables. My migration script will work fine with the queries above, so actually I do not have a blocking issue. The reason for this question is that I am just curious about the apparent differences between SQL Server and PostgreSQL regarding the AT TIME ZONE behavior and the ways to overcome those behavioral differences.

Answer 1

In postgresql, AT TIME ZONE allows time zones to be added to date/time values that lack them (TIMESTAMP WITHOUT TIME ZONE, ::timestamp), and allows TIMESTAMP WITH TIME ZONE values (::timestamptz) to be shifted to non-local time zones and the time zone designation removed.

So, in the first query you've written for postgres, you're trying to cast '2020-06-20 12:34:56.789' to timestamp, and you've applied AT TIME ZONE on it for LocalTimeWithOffset. This is the case where postgres considered the input as timestamp in UTC and converted to the corresponding time zone, in your case WET, which is UTC+2:00 in Summer and UTC+1:00 in winter.

Now, in the third case, you're first applying the AT TIME ZONE 'WET' to timestamp '2020-06-20 12:34:56.789', hence the input is considered as UTC and converted to WET, the output is timstamptz, and this output is then fed again to AT TIME ZONE 'UTC', so as per behavior of AT TIME ZONE, it gets converted to UTC and the timezone tag is removed, which is, the input we given.

So, AT TIME ZONE did not behaved weird in postgresql rather did exactly what it was supposed to do, according to the documentation.

Hope your confusion is cleared.