feat: EXPOSED-43 Add support for timestamp with time zone by joc-a · Pull Request #1787 · JetBrains/Exposed

joc-a · 2023-07-07T19:00:59Z

The data type timestamp with time zone is supported by the following databases:

It's not supported by the following databases:

MariaDB

Note: PostgreSQL and MySQL always store the timestamp in UTC, thereby losing the original time zone. To preserve the original time zone, store the time zone information in a separate column.

joc-a · 2023-07-14T11:04:53Z

@bog-walk @e5l @Tapac Still need to address the failing default test, but requesting some feedback in the meantime.

exposed-java-time/src/test/kotlin/org/jetbrains/exposed/DefaultsTest.kt

exposed-java-time/src/main/kotlin/org/jetbrains/exposed/sql/javatime/JavaDateColumnType.kt

e5l

@Tapac, what do you think?

exposed-java-time/src/test/kotlin/org/jetbrains/exposed/DefaultsTest.kt

joc-a · 2023-07-24T19:33:33Z

exposed-jodatime/src/test/kotlin/org/jetbrains/exposed/JodaTimeTests.kt

 }

-val today: DateTime = DateTime.now().withTimeAtStartOfDay()
+val today: DateTime = DateTime.now(DateTimeZone.UTC).withTimeAtStartOfDay()


Several tests in exposed-jodatime module were failing because they were expecting today to be in UTC time zone (as set in the init block), but were getting it in Tokyo time zone. I tried to set the time zone to UTC both before and after each test like this, but it didn't fix the failing tests.

@Before @After fun setTimeZone() { DateTimeZone.setDefault(DateTimeZone.UTC) }

Specifying the time zone fixed it.

…1787) The data type timestamp with time zone is supported by the following databases: -H2 -Oracle -SQLite -SQL Server -PostgreSQL (converted to UTC when stored, without preserving time zone info) -MySQL 8.0.19+ (converted to UTC when stored, without preserving time zone info) It's not supported by the following databases: -MariaDB Note: PostgreSQL and MySQL always store the timestamp in UTC, thereby losing the original time zone. To preserve the original time zone, store the time zone information in a separate column.

Maxr1998 · 2024-03-04T14:21:56Z

@joc-a sorry for the bump. This pull request implements this feature by using OffsetDateTime, which makes sense if you need a specific timezone, but there isn't any option for this when using java.time.Instant. Of course, it's possible to convert from/to it, but this introduces unnecessary boilerplate. Are there any plans to introduce a TIMEZONE WITH TIMESTAMP column type using Instants as well (storing the timezone as UTC where supported)?
There was an attempt once in #1223, but it was superseded by this PR.

I can create a new issue for this if you think this feature is useful.

Looking at the code, this should be straightforward to implement:

JavaTimeZoneInstantColumnType

class JavaTimeZoneInstantColumnType : ColumnType(), IDateColumnType {
    override val hasTimePart: Boolean = true

    override fun sqlType(): String = currentDialect.dataTypeProvider.timestampWithTimeZoneType()

    override fun nonNullValueToString(value: Any): String = when (value) {
        is Instant -> {
            val offsetDateTime = value.atOffset(ZoneOffset.UTC)
            when (currentDialect) {
                // is SQLiteDialect -> "'${offsetDateTime.format(SQLITE_OFFSET_DATE_TIME_FORMATTER)}'"
                // is MysqlDialect -> "'${offsetDateTime.format(MYSQL_OFFSET_DATE_TIME_FORMATTER)}'"
                // is OracleDialect -> "'${offsetDateTime.format(ORACLE_OFFSET_DATE_TIME_FORMATTER)}'"
                else -> "'${offsetDateTime.format(DateTimeFormatter.ISO_OFFSET_DATE_TIME.withLocale(Locale.ROOT))}'"
            }
        }
        else -> error("Unexpected value: $value of ${value::class.qualifiedName}")
    }

    override fun valueFromDB(value: Any): Instant = when (value) {
        is Instant -> value
        is String -> {
            val offsetDateTime = when (currentDialect) {
                // is SQLiteDialect -> OffsetDateTime.parse(value, SQLITE_OFFSET_DATE_TIME_FORMATTER)
                else -> OffsetDateTime.parse(value)
            }
            offsetDateTime.toInstant()
        }
        else -> error("Unexpected value: $value of ${value::class.qualifiedName}")
    }

    override fun readObject(rs: ResultSet, index: Int): Any? = when (currentDialect) {
        is SQLiteDialect -> super.readObject(rs, index)
        else -> rs.getObject(index, OffsetDateTime::class.java)?.toInstant()
    }

    override fun notNullValueToDB(value: Any): Any = when (value) {
        is Instant -> {
            val offsetDateTime = value.atOffset(ZoneOffset.UTC)
            when (currentDialect) {
                // is SQLiteDialect -> offsetDateTime.format(SQLITE_OFFSET_DATE_TIME_FORMATTER)
                // is MysqlDialect -> offsetDateTime.format(MYSQL_OFFSET_DATE_TIME_FORMATTER)
                else -> offsetDateTime
            }
        }
        else -> error("Unexpected value: $value of ${value::class.qualifiedName}")
    }

    companion object {
        internal val INSTANCE = JavaTimeZoneInstantColumnType()
    }
}

The dialects are only commented out because the formatters are private, but they should work just as well.

joc-a force-pushed the joc/timestamp-with-time-zone-support branch 9 times, most recently from f984109 to 6b0873a Compare July 14, 2023 10:26

joc-a requested review from bog-walk and e5l July 14, 2023 10:58

joc-a force-pushed the joc/timestamp-with-time-zone-support branch from 6b0873a to 3aaffb3 Compare July 14, 2023 11:03

joc-a requested a review from Tapac July 14, 2023 11:05

joc-a force-pushed the joc/timestamp-with-time-zone-support branch from 3aaffb3 to 2bea53d Compare July 14, 2023 11:05

joc-a changed the title ~~feat: Add support for timestamp with time zone~~ feat: EXPOSED-43 Add support for timestamp with time zone Jul 14, 2023

bog-walk reviewed Jul 14, 2023

View reviewed changes

exposed-java-time/src/test/kotlin/org/jetbrains/exposed/DefaultsTest.kt Outdated Show resolved Hide resolved

exposed-java-time/src/main/kotlin/org/jetbrains/exposed/sql/javatime/JavaDateColumnType.kt Show resolved Hide resolved

e5l approved these changes Jul 17, 2023

View reviewed changes

exposed-java-time/src/test/kotlin/org/jetbrains/exposed/DefaultsTest.kt Outdated Show resolved Hide resolved

joc-a force-pushed the joc/timestamp-with-time-zone-support branch 11 times, most recently from 6398ba7 to a877cf1 Compare July 19, 2023 12:15

joc-a marked this pull request as ready for review July 19, 2023 14:56

joc-a force-pushed the joc/timestamp-with-time-zone-support branch 10 times, most recently from ead4383 to dd4912b Compare July 24, 2023 17:28

feat: EXPOSED-43 Add support for timestamp with time zone

4932c28

joc-a force-pushed the joc/timestamp-with-time-zone-support branch from dd4912b to 4932c28 Compare July 24, 2023 19:20

joc-a commented Jul 24, 2023

View reviewed changes

joc-a requested a review from bog-walk July 24, 2023 19:39

bog-walk approved these changes Jul 26, 2023

View reviewed changes

joc-a merged commit a3830ed into main Jul 27, 2023

joc-a deleted the joc/timestamp-with-time-zone-support branch July 27, 2023 11:50

This was referenced Jul 27, 2023

Add timezone to DateTime column type #109

Closed

Add support for datetime with timezone #1079

Closed

Issue 221 - Map java.time.Instant to type stored in UTC #1223

Closed

michael-markl mentioned this pull request Jun 11, 2025

1981.2: Approve application for Ehrenamtskarte Bayern digitalfabrik/entitlementcard#2249

Merged

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

Comments

feat: EXPOSED-43 Add support for timestamp with time zone#1787

feat: EXPOSED-43 Add support for timestamp with time zone#1787
joc-a merged 1 commit intomainfrom
joc/timestamp-with-time-zone-support

joc-a commented Jul 7, 2023 •

edited

Loading

Uh oh!

joc-a commented Jul 14, 2023

Uh oh!

Uh oh!

Uh oh!

e5l left a comment

Uh oh!

Uh oh!

joc-a Jul 24, 2023

Uh oh!

Maxr1998 commented Mar 4, 2024 •

edited

Loading

Uh oh!

Reviewers

Assignees

Labels

Projects

Milestone

Development

Uh oh!

4 participants

Comments

Conversation

joc-a commented Jul 7, 2023 • edited Loading Uh oh! There was an error while loading. Please reload this page.

Uh oh!

Uh oh!

joc-a commented Jul 14, 2023

Uh oh!

Uh oh!

Uh oh!

e5l left a comment

Choose a reason for hiding this comment

Uh oh!

Uh oh!

joc-a Jul 24, 2023

Choose a reason for hiding this comment

Uh oh!

Maxr1998 commented Mar 4, 2024 • edited Loading Uh oh! There was an error while loading. Please reload this page.

Uh oh!

Uh oh!

Reviewers

Assignees

Labels

Projects

Milestone

Development

Uh oh!

4 participants

joc-a commented Jul 7, 2023 •

edited

Loading

Maxr1998 commented Mar 4, 2024 •

edited

Loading