postgres-java-time

Exposed PostgreSQL Timestamp Support (Java Time)

Instant timestamp(...) with PostgreSQL's TIMESTAMP WITH TIMEZONE.

• Avoids the issue described in #1536, #1305, #886
• Here's why you should use TIMESTAMP WITH TIMEZONE: https://www.toolbox.com/tech/data-management/blogs/zone-of-misunderstanding-092811/

Example:

ExposedPowerUtils/tests/src/test/kotlin/PostgresJavaTimeTest.kt

Lines 23 to 60 in 80fa05d

	transaction(test) {
	SchemaUtils.createMissingTablesAndColumns(ActionLog)
	// Exposed's "timestamp" implementation has an issue where it depends on the system's time zone.
	// So if you inserted "Instant.now()", it would depend on your system time zone, and that's not good!
	// Imagine if you have two machines in different time zones, and you are trying to keep an audit log,
	// even if both codes were executed at the same time, due to time zone differences, both times would
	// be different!
	//
	// If you ran the following code with Exposed's "timestamp", it will fail.
	//
	// That's why we use the "timestampWithTimeZone", read more at https://www.toolbox.com/tech/data-management/blogs/zone-of-misunderstanding-092811/
	val now = Instant.now()
	TimeZone.setDefault(TimeZone.getTimeZone("UTC"))
	val id1 = ActionLog.insertAndGetId {
	it[ActionLog.timestamp] = now
	it[ActionLog.text] = "Hello from UTC!"
	}
	TimeZone.setDefault(TimeZone.getTimeZone("America/Sao_Paulo"))
	val id2 = ActionLog.insertAndGetId {
	it[ActionLog.timestamp] = now
	it[ActionLog.text] = "Hello from America/Sao_Paulo!"
	}
	TimeZone.setDefault(TimeZone.getTimeZone("UTC"))
	val timezone1 = ActionLog.select { ActionLog.id eq id1 }.first()[ActionLog.timestamp]
	val timezone2 = ActionLog.select { ActionLog.id eq id2 }.first()[ActionLog.timestamp]
	assert(timezone1 == timezone2) { "The instants aren't equal! $timezone1 $timezone2" }
	}
	}
	object ActionLog : LongIdTable() {
	val timestamp = timestampWithTimeZone("timestamp")
	val text = text("text")
	}