CODEWITHSUNDEEP
databasepostgresql
Christian
Christian

Reputation: 7429

Truncate a date in Postgres (latest version)

I have different timestamps within my posgres db. With timestamp I mean unix time since 1970 saved as bigint.

Now I need to get the timestamp since 1970 striped down to days. For instance: 1469059200000 (Today: 2:00 am should become 1469052000000 (Today: 00:00 am)

I've tried this in pgadmin

Select date_trunc('day', rp.timestamp) from rollup_days as rp

but just got this error. ERROR: function date_trunc(unknown, bigint) does not exist

Upvotes: 1

Views: 4917

Answers (1)

user330315
user330315

Reputation:

A bigint is not "a timestamp", so you must convert the number to a date before you can apply date_trunc() on it:

Select date_trunc('day', to_timestamp(rp.timestamp)) 
from rollup_days as rp;

To convert the timestamp back to a bigint, use extract()

Select extract(epoch from date_trunc('day', to_timestamp(rp.timestamp)))
from rollup_days as rp;

Upvotes: 3

Related Questions