Why is Java LocalTime 01:32:13.283256 stored as time 00:32:13 in database? 1 hour behind

Question

Why is the value set to 1 hour behind?

If Europe/Dublin or Europe/London is same offset as UTC, why is the value in db different to originating JVM value?

JVM: Europe/Dublin - TimeZone.getDefault().getID() or
JVM: Europe/London - TimeZone.getDefault().getID()

// LocalTime.ofInstant(now, Clock.systemDefaultZone().getZone())
01:32:13.283256

Which is same as UTC currently, no offset.

Time zone for JDBC connections set for Spring / Hibernate using the configuration property:

spring.jpa.properties.hibernate.jdbc.time_zone=UTC

Datebase query:

select * from item;

 id | local_time 
----+------------
  1 | 00:32:13

local_time has time column type.

Hmm, the behaviour sounds like it is `time with time zone` (= `timetz`). — , Dec 07 '22 at 14:19
Could it be that the JVM is configured with a different timezone than jdbc timezone ? — Stefanov.sm, Dec 07 '22 at 14:54
@a_horse_with_no_name column type is `time`/`time without time zone` — bobbyrne01, Dec 07 '22 at 15:41
@Stefanov.sm JVM is `Europe/Dublin` and JDBC connections are `UTC` .. both of which are same time, as outlined in question. — bobbyrne01, Dec 07 '22 at 15:42
Follow-up question to [Why are Java LocalTime and OffsetTime values incorrect when viewed directly on database](https://stackoverflow.com/questions/74696387/why-are-java-localtime-and-offsettime-values-incorrect-when-viewed-directly-on-d). And yes, there is some time zone trouble involved. It may or not matter that Ireland was at offset +01:00 at the epoch in 1970. And there *might* be a bug in the database or database driver involved, but not that likely. — Ole V.V., Dec 07 '22 at 15:50
@PepeNO `show TimeZone;` shows `Etc/UTC` when running `psql` inside PostgreSQL container — bobbyrne01, Dec 13 '22 at 23:47
It seems to be a bug. Instead of waiting for an answer (which can be most likely based on guesses), you should raise a ticket with the Postgresql community and/or support team. — Arvind Kumar Avinash, Dec 14 '22 at 16:00
Insert your time as string and as time in your database to debbug, I reproduced your time zones in both sides and see no difference. — Pepe N O, Dec 14 '22 at 23:09

Andrew · Accepted Answer · 2022-12-14T13:03:28.700

This is is reflecting the fact that in 1970 Dublin was UTC+1 all year.

From the europe file in the TZ database:

# From Paul Eggert (2018-02-15):
# In January 2018 we discovered that the negative SAVE values in the
# Eire rules cause problems with tests for ICU:
# https://mm.icann.org/pipermail/tz/2018-January/025825.html
# and with tests for OpenJDK:
# https://mm.icann.org/pipermail/tz/2018-January/025822.html
#
# To work around this problem, the build procedure can translate the
# following data into two forms, one with negative SAVE values and the
# other form with a traditional approximation for Irish timestamps
# after 1971-10-31 02:00 UTC; although this approximation has tm_isdst
# flags that are reversed, its UTC offsets are correct and this often
# suffices.  This source file currently uses only nonnegative SAVE
# values, but this is intended to change and downstream code should
# not rely on it.
#
# The following is like GB-Eire and EU, except with standard time in
# summer and negative daylight saving time in winter.  It is for when
# negative SAVE values are used.
# Rule  NAME    FROM    TO      -       IN      ON      AT      SAVE    LETTER/S
Rule    Eire    1971    only    -       Oct     31       2:00u  -1:00   -
Rule    Eire    1972    1980    -       Mar     Sun>=16  2:00u  0       -
Rule    Eire    1972    1980    -       Oct     Sun>=23  2:00u  -1:00   -
Rule    Eire    1981    max     -       Mar     lastSun  1:00u  0       -
Rule    Eire    1981    1989    -       Oct     Sun>=23  1:00u  -1:00   -
Rule    Eire    1990    1995    -       Oct     Sun>=22  1:00u  -1:00   -
Rule    Eire    1996    max     -       Oct     lastSun  1:00u  -1:00   -

# Zone  NAME            STDOFF  RULES   FORMAT  [UNTIL]
                #STDOFF -0:25:21.1
Zone    Europe/Dublin   -0:25:21 -      LMT     1880 Aug  2
                        -0:25:21 -      DMT     1916 May 21  2:00s
                        -0:25:21 1:00   IST     1916 Oct  1  2:00s
                         0:00   GB-Eire %s      1921 Dec  6 # independence
                         0:00   GB-Eire GMT/IST 1940 Feb 25  2:00s
                         0:00   1:00    IST     1946 Oct  6  2:00s
                         0:00   -       GMT     1947 Mar 16  2:00s
                         0:00   1:00    IST     1947 Nov  2  2:00s
                         0:00   -       GMT     1948 Apr 18  2:00s
                         0:00   GB-Eire GMT/IST 1968 Oct 27
# Vanguard section, for zic and other parsers that support negative DST.
                         1:00   Eire    IST/GMT
# Rearguard section, for parsers lacking negative DST; see ziguard.awk.
#                        1:00   -       IST     1971 Oct 31  2:00u
#                        0:00   GB-Eire GMT/IST 1996
#                        0:00   EU      GMT/IST
# End of rearguard section.

London has the same issue:

# Zone  NAME            STDOFF  RULES   FORMAT  [UNTIL] 
Zone    Europe/London   -0:01:15 -      LMT     1847 Dec  1
                         0:00   GB-Eire %s      1968 Oct 27
                         1:00   -       BST     1971 Oct 31  2:00u
                         0:00   GB-Eire %s      1996    
                         0:00   EU      GMT/BST 

# From Paul Eggert (2018-02-15):
# In January 2018 we discovered that the negative SAVE values in the
# Eire rules cause problems with tests for ICU:
# https://mm.icann.org/pipermail/tz/2018-January/025825.html
# and with tests for OpenJDK:
# https://mm.icann.org/pipermail/tz/2018-January/025822.html
#
# To work around this problem, the build procedure can translate the
# following data into two forms, one with negative SAVE values and the 
# other form with a traditional approximation for Irish timestamps
# after 1971-10-31 02:00 UTC; although this approximation has tm_isdst
# flags that are reversed, its UTC offsets are correct and this often
# suffices.  This source file currently uses only nonnegative SAVE
# values, but this is intended to change and downstream code should
# not rely on it.
#
# The following is like GB-Eire and EU, except with standard time in 
# summer and negative daylight saving time in winter.  It is for when
# negative SAVE values are used.
# Rule  NAME    FROM    TO      -       IN      ON      AT      SAVE    LETTER/S
Rule    Eire    1971    only    -       Oct     31       2:00u  -1:00   -
Rule    Eire    1972    1980    -       Mar     Sun>=16  2:00u  0       -       
Rule    Eire    1972    1980    -       Oct     Sun>=23  2:00u  -1:00   -
Rule    Eire    1981    max     -       Mar     lastSun  1:00u  0       -       
Rule    Eire    1981    1989    -       Oct     Sun>=23  1:00u  -1:00   -
Rule    Eire    1990    1995    -       Oct     Sun>=22  1:00u  -1:00   -
Rule    Eire    1996    max     -       Oct     lastSun  1:00u  -1:00   -

I'm using `OpenJDK Runtime Environment Temurin-17.0.5+8` and that bug refers to fixes in java11 /13 .. shouldn't this jvm already have that fix? Any way to confirm? - considering the issue still occurs for me — bobbyrne01, Dec 13 '22 at 18:47
@bobbyrne01 Looking more closely, in 1970, Dublin was UTC+1. See https://community.oracle.com/tech/developers/discussion/2066789/bug-in-java-5-date-calendar-timezone-implementation-possibly-6-also, https://www.timeanddate.com/time/zone/ireland/dublin. Also, read the whole section in the TZ database europe file (I am editing my answer now to include it...) — Andrew, Dec 13 '22 at 22:31
"in 1970, Dublin was UTC+1", this might explain why `LocalTime` is 1 hour behind after save (Dub -> UTC = -1) .. i see same behaviour if JVM is `Europe/London` .. since multiple timezones see this difference in value, does that mean it could still be a java bug? — bobbyrne01, Dec 14 '22 at 00:28
yup. see https://www.timeanddate.com/time/zone/uk/london at 1970 (drop down menu). So maybe not a bug, but Dublin is mentioned specifically in the TZ europe file, as is OpenJDK (see the link https://mm.icann.org/pipermail/tz/2018-January/025822.html from that file). — Andrew, Dec 14 '22 at 12:35
same thing in London; just checked the file; updating my answer now... — Andrew, Dec 14 '22 at 12:55
Whats the expected fix here from an application perspective? Is this indicating `LocalTime` can not be reliably stored to Postgres as a `time` when dealing with multiple timezones? — bobbyrne01, Dec 14 '22 at 13:25
We have answered the question (Why is the value set to 1 hour behind?), right? As far as the fix, one might cite the "if it ain't broke, don't fix it adage", load a tz db that predates this change (https://bugs.openjdk.org/browse/JDK-8195595 indicates this would be ones — Andrew, Dec 14 '22 at 15:12
Read the links I've provided, that might help. Or contact one (or more) of the folks involved in this on the JDK and/or TZ side. — Andrew, Dec 14 '22 at 15:13
Timezones and daylight savings time are set by government decree and as such they are somewhat unreliable, using flat UTC solves most issues and that is what most large ISPs do (leap seconds are a whole 'nutha mess though!). — Andrew, Dec 14 '22 at 15:13

Why is Java LocalTime 01:32:13.283256 stored as time 00:32:13 in database? 1 hour behind

1 Answers1

Linked