Thread: Reading timestamp values from Datums gives garbage values

On 5/20/24 16:37, Sushrut Shivaswamy wrote:
> Hey,
> 
> I'm trying to read a timestamp column as EPOCH.
> My query is as follows.
> ```
> SELECT EXTRACT(EPOCH FROM timestamp_column) FROM table;
> 
> column
> ----------
> 
> 1716213097.86486
> ```
> When running in the console this query gives valid epoch output which
> appears to be of type double.
> 
> When trying to read the query response from the Datum, I get garbage values.
> I've tried various types and none of them read the correct value.
> ```
> 
> Datum current_timestamp = SPI_getbinval(SPI_tuptable->vals[i],
> SPI_tuptable->tupdesc, 5, &isnull);
> 
> 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.

regards

-- 
Tomas Vondra
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

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

Tomas Vondra <tomas.vondra@enterprisedb.com> writes:
> On 5/20/24 16:37, Sushrut Shivaswamy wrote:
>> When trying to read the query response from the Datum, I get garbage values.
>> I've tried various types and none of them read the correct value.

> 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.

Don't forget that TimestampTz uses an epoch (time zero) of 2000-01-01.
If you want a Unix-convention value where the epoch is 1970-01-01,
you'll need to add 30 years to the result.

The reported values seem pretty substantially off, though ---
5293917674 would be barely an hour and a half later than the
epoch, which seems unlikely to be the value Sushrut intended
to test with.  I suspect a mistake that's outside the fragment
of code we were shown.

            regards, tom lane

Hi,

> When trying to read the query response from the Datum, I get garbage values.
> I've tried various types and none of them read the correct value.
> ```
>
> Datum current_timestamp = SPI_getbinval(SPI_tuptable->vals[i], SPI_tuptable->tupdesc, 5, &isnull);
>
> double current_time = DatumGetFloat8(current_timestamp); // prints 0
>
> int64 time = DatumGetUint64(current_timestamp); // prints 5293917674
>
> ```
>
> Can you help me out with the correct way to read EPOCH values from datums?

I don't entirely understand why you are using DatumGetFloat8() /
DatumGetUint64() and double / int64 types. There are
DatumGetTimestamp() / DatumGetTimestampTz() and Timestamp /
TimestampTz.

I recommend using the PostgreSQL code as a source of more examples of
how to deal with the given types. The file pg_proc.dat is a good entry
point. See also commit 260a1f18 [1] and PostgreSQL documentation [2].

[1]: https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=260a1f18
[2]: https://www.postgresql.org/docs/16/xfunc-c.html

-- 
Best regards,
Aleksander Alekseev