Re: Reading timestamp values from Datums gives garbage values

On 05/20/24 11:39, Tomas Vondra wrote:
> On 5/20/24 16:37, Sushrut Shivaswamy wrote:
>> I've tried various types and none of them read the correct value.
>> ```
>> ...
>> double current_time = DatumGetFloat8(current_timestamp); // prints 0
>>
>> int64 time = DatumGetUint64(current_timestamp); // prints 5293917674
>> ```
>
> TimestampTz is int64, so using DatumGetInt64 is probably the simplest
> solution. And it's the number of microseconds, so X/1e6 should give you
> the epoch.

Indeed, the "Postgres epoch" is a fairly modern date (1 January 2000),
so a signed representation is needed to express earlier dates.

Possibly of interest for questions like these, some ongoing work in PL/Java
is to capture knowledge like this in simple Java functional interfaces
that are (intended to be) sufficiently clear and documented to serve as
a parallel source of reference matter.

For example, what's there for TimestampTZ:

https://tada.github.io/pljava/preview1.7/pljava-api/apidocs/org.postgresql.pljava/org/postgresql/pljava/adt/Datetime.TimestampTZ.html#method-detail

A separation of concerns is involved, where these functional interfaces
expose and document a logical structure and, ideally, whatever semantic
subtleties may be inherent in it, but not physical details of how those
bits might be shoehorned into the Datum. Physical layouts are encapsulated
in Adapter classes as internal details. TimeTZ is a good example:

https://tada.github.io/pljava/preview1.7/pljava-api/apidocs/org.postgresql.pljava/org/postgresql/pljava/adt/Datetime.TimeTZ.html#method-detail

It tells you of the µsSinceMidnight component, and secsWestOfPrimeMeridian
component, and the sign flip needed for other common representations of
zone offsets that are positive _east_ of the prime meridian. It doesn't
expose the exact layout of those components in a Datum.

For your purposes, of course, you need the physical layout details too,
most easily found by reading the PG source. But my hope is that this
parallel documentation of the logical structure may help in making
effective use of what you find there.

Regards,
-Chap