How to disable conversion to UTC when storing OffsetDateTime in PostgreSQL

Question

Given this table:

CREATE TABLE a(
  t TIMESTAMP WITH TIME ZONE
);

And this simple JDBC code snippet:

DriverManager.getConnection(
    "jdbc:postgresql://localhost:5432/dbname", "user", "password"
).use { connection ->
    val nowSomeTimeZone = OffsetDateTime.now(ZoneOffset.of("+4"))
    connection.prepareStatement("insert into a(t) values (?)").use { insertStmt ->
        insertStmt.setObject(1, nowSomeTimeZone)
        insertStmt.executeUpdate()
    }
    connection.createStatement().use { stmt ->
        stmt.executeQuery("select * from a").use { resultSet ->
            resultSet.next()
            val t = resultSet.getObject(1, OffsetDateTime::class.java)
            println("$nowSomeTimeZone -> $t")
        }
    }
}

Somewhere inside the JDBC stack an automatic conversion from +04:00 to UTC must be happening, because this is the println output:

2018-08-30T10:35:33.594+04:00 -> 2018-08-30T06:35:33.594Z

What's even more weird, when I look into the table using the psql console client, it shows me the timestamp in yet another time zone (which is my local time zone):

$ psql -h localhost -U username
dbname=> select * from a;
             t              
----------------------------
 2018-08-30 08:35:33.594+02

Why does this conversion happen, and how can I disable it?

From their documentation: "Also note that all OffsetDateTime will instances will have be in UTC (have offset 0). This is because the backend stores them as UTC." — Robby Cornelissen
– Robby Cornelissen, Commented Aug 30, 2018 at 9:08
Thanks @RobbyCornelissen but this does not seem to be correct - as you can see from the command-line output the backend does store the +2 hours offset. I think this remark applies only when you use the datatype TIMESTAMP WITHOUT TIME ZONE — Bastian Voigt
– Bastian Voigt, Commented Aug 30, 2018 at 9:12
No, I think it's correct. It also corresponds with the documentation here. (Check the last paragraphs of section "8.5.1.3. Time Stamps") — Robby Cornelissen
– Robby Cornelissen, Commented Aug 30, 2018 at 9:20

Bastian Voigt · Accepted Answer · 2018-08-30 15:47:32Z

Disabling the conversion is not possible, because the PostgreSQL server strips the time zone information and stores timestamps always in UTC, even when you are explicitly using the type TIMESTAMP WITH TIME ZONE.

Quoting the PostgreSQL documentation:

For timestamp with time zone, the internally stored value is always in UTC (Universal Coordinated Time, traditionally known as Greenwich Mean Time, GMT). An input value that has an explicit time zone specified is converted to UTC using the appropriate offset for that time zone. If no time zone is stated in the input string, then it is assumed to be in the time zone indicated by the system's TimeZone parameter, and is converted to UTC using the offset for the timezone zone.

Moreover, the documentation states:

We do not recommend using the type time with time zone (though it is supported by PostgreSQL for legacy applications and for compliance with the SQL standard). PostgreSQL assumes your local time zone for any type containing only date or time.

The weird behaviour described in the question is because

The JDBC driver always returns the timestamp in UTC
The psql console client converts the timestamp to the user's local time zone before displaying it, in this case German Time (+02:00)

Thanks to @RobbyCornelissen for the insights.

Collectives™ on Stack Overflow

How to disable conversion to UTC when storing OffsetDateTime in PostgreSQL

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related