Postgresql timestamp with tomezone

[siksniraps]

In Postgresql TIMESTAMP defaults to TIMESTAMP WITHOUT TIMEZONE. It could be very useful to make it possible to also add TIMESTAMP WITH TIMEZONE.

[NagyGa1]

Same for MySQL's TIMESTAMP and H2'S TIMESTAMP WITH TIME ZONE. Instants best persisted as long at the moment.

[tnleeuw]

From my understanding of the Java Instant type it has an implied UTC timezone. Using Exposed 0.31.1 with the java-time library, it is stored as a SQL timestamp without timezone, so values are translated. This makes it harder to match DB values with application values, and trying to store "max timestamp" value of 9999-12-31T23:59:59.999 is now translated to 10000-01-01T00:59:59.999, which is not a valid date-value in Postgres.

I think that at least for java.time.Instant, this should be mapped to a timestamp with timezone in databases.

[tnleeuw]

I think I've fixed this issue by disabling the option "Use gRPC Fuse filesystem for filesharing" in Docker options and now tests are running (and failing with proper errors that can be expected! 😛 )

~PS: I'm trying to write a fix for this issue but am having trouble to build locally; the test-containers are giving me trouble. A lot of:~

org.jetbrains.exposed.JavaTimeBaseTest > javaTimeFunctions FAILED
    org.testcontainers.containers.ContainerLaunchException: Container startup failed
...
        Caused by:
        org.testcontainers.containers.ContainerFetchException: Can't get Docker image: RemoteDockerImage(imageName=mysql:5, imagePullPolicy=DefaultPullPolicy())
            at org.testcontainers.containers.GenericContainer.getDockerImageName(GenericContainer.java:1279)
            at org.testcontainers.containers.GenericContainer.logger(GenericContainer.java:613)
            at org.testcontainers.containers.GenericContainer.doStart(GenericContainer.java:320)
            ... 12 more

            Caused by:
            com.github.dockerjava.api.exception.ConflictException: {"message":"Conflict. The container name \"/testcontainers-ryuk-beffe74f-811a-4802-9aaa-94b8dd684ce6\" is already in use by container \"8663881679a85ebe4a985dfbea433dfa744e2134a30996bfc121a188ac7854fb\". You have to remove (or rename) that container to be able to reuse that name."}

~Of course, prior to me running the tests no such container exists (I'm careful to delete it). Perhaps I do have a failing test somewhere, but this exception all over the place makes it hard for me to find those failures!~

~Any hints for overcoming this?~

[tnleeuw]

@Tapac Here are initial changes which I did for this issue.

Issue_221_-Initial_changes_for_mappingjava_time_Instant_to_timestamp_with_timezone_typ.patch.zip

I have some test failures but I'm not sure how to proceed:

1. I get a lot of errors on tests with embedded Postgres. However I get these both in master and in a branch with my changes:

org.jetbrains.exposed.DefaultsTest > testBatchInsertFails01 FAILED
    java.lang.IllegalStateException: Process [/var/folders/fx/rkm6hc997w78m9t08fx4d69h0000gn/T/embedded-pg/PG-4e61fcb9c1d914c1a435587f4504778d/bin/initdb, -A, trust, -U, postgres, -D, /var/folders/fx/rkm6hc997w78m9t08fx4d69h0000gn/T/epg13389674472446053055, -E, UTF-8] failed
        at com.opentable.db.postgres.embedded.EmbeddedPostgres.system(EmbeddedPostgres.java:585)
        at com.opentable.db.postgres.embedded.EmbeddedPostgres.initdb(EmbeddedPostgres.java:227)
        at com.opentable.db.postgres.embedded.EmbeddedPostgres.<init>(EmbeddedPostgres.java:145)

2. I have a number of tests failing on MySQL value comparisons for java.time.Instant values. However it seems that all these errors are rounding-errors on milliseconds, and I cannot get a debugger to hit a breakpoint that I set in the comparison methods for the dates (or anywhere else in the tests) to figure out what's going on. I'll add some logging to see if I can get more insight that way.

Meanwhile, I'd appreciate if someone could have a look at this patch to see if I'm more or less on the right way, and if work on this has a chance of being accepted for an upcoming release.

Here is a draft PR that I opened for this issue so you don't have to look at a zipped patch file:

https://github.com/JetBrains/Exposed/pull/1223

[tnleeuw]

Fixed the issue with timestamp precision in MySQL. Still no idea on why embedded Postgres fails in all tests.

[Jerbell]

Would love some progress on this. Is there any way to work around this?

[wyaeld]

@Tapac this causes some real issues where we have an App running in a country specific timezone, and a Cloud DB running in a default UTC. It appears people have put most of the work together to support "timestamp with timezone", can you offer feedback on whether that can be added to Exposed.

[Jerbell]

For whatever reason they don't seem interested. Possibly because the solution exposes some issues I pointed out here: #1370 I created a class based heavily on https://github.com/JetBrains/Exposed/pull/1223/files JavaDateColumnType.kt file. It works for me.

[MrPowerGamerBR]

Here's my implementation of a TIMESTAMP WITH TIME ZONE, only works in PostgreSQL: https://github.com/PerfectDreams/ExposedPowerUtils/tree/main/postgres-java-time/src/main/kotlin/net/perfectdreams/exposedpowerutils/sql/javatime