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
