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
