TIMESTAMPTZ actually means time zone agnostic. The description of these types do not reflect their actually usage. You are using TIMESTAMP instead of TIMESTAMPTZ.When you insert or query data the database server will always use the JVM time zone. So even if your Database Server is running in UTC a TIMESTAMP field will be inserted using the time zone of your JVM. The PostgreSQL JDBC driver sets the timezone to your JVM timezone in the Postgres session. Unfortunately you have a few things working against you: How can I have jOOQ create timestamps NOT in local time? Given the time is being supplied in a TimeZone agnostic container (Timestamp) I would have expected that to be honoured when creating the insert statement. The software is running on a machine in NZDT which explains the +13 offset. What I'm seeing, during insert, is jOOQ's binding process converting a into a date with local timezone offset.Įg for a unix timestamp 1421109419 ( 00:36:59 GMT) the property is set with new Timestamp(1421109419 * 1000).įrom the jOOQ logger I see: 14:14:31,482 DEBUG #debug:255 -> with bind values : insert into "foo"."bar" ("start_date") values (timestamp ' 13:36:59.0') returning "foo"."bar"."id" I have a table with a column of type timestamp without time zone, this generates my Object with the applicable property. The type time with time zone is defined by the SQL standard, but the definition exhibits properties which lead to questionable usefulness.PostgreSQL 9.3 / Int32 zone /* numeric time zone, in seconds */įollow the documentation and avoid time with time zone: TimeADT time /* all time units other than months and years */ See src/include/utils/date.h: typedef struct Time with time zone has an additional 4 byte integer representing the time zone offset in seconds. See src/include/utils/date.h: typedef int64 TimeADT Time without time zone is a 8 byte integer representing microseconds since midnight (a 4 byte integer wouldn't suffice). The definitions are in src/include/datatype/timestamp.h: typedef int64 Timestamp The difference is that timestamp with time zone is interpreted as being in UTC and is converted according to the current setting of the timezone parameter on display. Thus my question: How does the internal storage of these types in PostgreSQL work?īoth timestamp with time zone and timestamp without time zone are stored as an 8 byte integer representing microseconds since midnight on Jan 1 2000. If we already use 63 bits for the values there is only one bit left, which cannot be enough to store the timezone, so the internal storage must work somehow different.Įven stranger, while timestamps use only 8 bytes, time with time zone needs 12 bytes, although it has the same resolution and a much smaller range of allowed values. However, timestamp with timezone has the same range and resolution and can additonally store the time zone information. This amounts to roughly 2^63 values and those can be stored in 8 bytes. (Not accounting for calender changes and such). I can understand timestamp without time zone: There are roughly (4713 + 294276) * 365 * 24 * 60 * 60 * 1000000 microseconds between low and high value. Time with time zone 12 bytes time of day (no date), with time zone 00:00:00+1559 24:00:00-1559 1 microsecond Timestamp with time zone 8 bytes both date and time, with time zone 4713 BC 294276 AD 1 microsecond Timestamp without time zone 8 bytes both date and time (no time zone) 4713 BC 294276 AD 1 microsecond Inter alia it states: Name Storage Size Description Low Value High Value Resolution When reading the PostreSQL (13) documentation, I came across this page, which lists the storage sizes of different datetime types.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |