Re: 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