On 08/12/2023 11:38, tomas@tuxteam.de wrote:
On Thu, Dec 07, 2023 at 10:18:44PM -0600, Nicholas Geovanis wrote:All of these considerations are what brought Oracle to create a proprietary "datetime" datatype and use it to store all "real" dates/times. If you need a different format for display purposes or a human readable column, you can extract it and do that. But the internal representation will be driven by other needs.If anyone is looking for inspiration, I think what PostgreSQL does is one of the best and most complete implementations I've seen.
I know nothing concerning the datetime type in Oracle.Postgres stores timestamps as a numbers. Its power is reliable conversion to client time zone (or between time zones). "timestamp with time zone" is actually duration since epoch (UTC) and conversion to a time zone on select.
However storing local time might be tricky. A week may have 2 Fridays with the same date.
zdump -v America/Juneau America/Juneau Sat Oct 19 00:31:12 1867 UT = Sat Oct 19 15:33:31 1867 LMT isdst=0 gmtoff=54139 America/Juneau Sat Oct 19 00:31:13 1867 UT = Fri Oct 18 15:33:32 1867 LMT isdst=0 gmtoff=-32261 Some territories crossed the international date line.