Hi everyone, I have a question: For a clomun defin...
# exposed
k
Hi everyone, I have a question: For a clomun defined as
timestamptz
in postgres I used
timstamp(...)
(
Column<Instant>
from Java Time API extension for Exposed) and when I persist a record it uses my current timezone, but I would like to get it persisted in GMT so that I dont have to do that math when comparing the values while looking into the DB (I want
2021-02-14 15:06:07.66052+00
instead of
2021-02-14 16:06:07.66052+01
) how do I enforce this?
s
I am not sure there is a better way than setting the JVM default zone:
Copy code
TimeZone.setDefault(TimeZone.getTimeZone("UTC"))
k
That is not optimal, but it seems like it is not working anyway. I put it to a
@PostConstruct
method in a configuration class (I m using spring boot) and it doesn't seem to have any effect
j
@Kuba Petržílka I am not sure if exposed supports
timestampz
specifically yet. The effect you're seeing is probably related to serialization of the data. See here, my guess is your db is assigning the default timezone.
k
@spand @Joel Thanks for answers, the problem is probably in the file you linked
Copy code
13: DateTimeFormatter.ISO_LOCAL_DATE.withLocale(Locale.ROOT).withZone(ZoneId.systemDefault())
And the suggestion to change the default timezone doesn't work in my case.. but I think it is not a good option anyway.. I d like to choose which timezone I want to use per record not globaly per system ... so Exposed really lacks this support
j
I think that is for deserialization. Either way, good opportunity for a PR!
s
I dont think so. Pretty sure its a JDBC thing to use the default timezone when it passes
java.sql.Timestamp
to the database. I cant see any way around that other than maybe having something per dialect that sets the zone per connection.
k
@spand I did some investigation and I realized that at first place there is actually no diff between timestamp and timestamp tz in the postgresql database when it comes to the way how it is presisted.. it always stores just the epoch microseconds, the difference is only that when using timestamptz it does autoconversion from the specified timezone to UTC or uses the DB's default TZ if no offset/zone info is provided... and when reading it again uses the timezone/offset returned with the value from the DB instead of the default timezone in the app.. So the only thing is that it is currently not possible to specify the timestamp/offset while persisting the data, but it actually doesn't matter since it is not stored anyway, and for sake of normalizing the timestamp on the DB side basically any timezone is fine, the DB will convert it to GMT automatically.. it is however shame that postgres doesn't support zonedtimestamp natively