On 08/12/2023 11:38, [email protected] 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.
