Group by day from nanosecond timestamp

Question

I have a table column transaction_timestamp storing timestamps as epochs with nanosecond resolution.

How do I group and/or count by day? I guess I have to convert the nanosecond timestamp to milliseconds first. How can I do that?

I tried:

SELECT DATE_TRUNC('day', CAST((transaction_timestamp /pow(10,6))as bigint)), COUNT(*)
FROM transaction
GROUP BY DATE_TRUNC('day', transaction_timestamp)

which is does not work:

error: function date_trunc(unknown, bigint) does not exist

I also tried this:

SELECT DATE_TRUNC('day', to_timestamp(transaction_timestamp / 1000000000.0)),
 COUNT(*)
FROM transaction
GROUP BY DATE_TRUNC('day', transaction_timestamp)

You seem to be dividing a timestamp by dome function and casting that to an integer, then trying to treat that as if it is a timestamp? Why not just truncate the timestamp? — David Aldridge, Mar 12 '22 at 15:08
See the answer here [Nanosecond timestamp](https://stackoverflow.com/questions/71438725/what-kind-of-datestyle-can-this-be) — Adrian Klaver, Mar 12 '22 at 16:12
how should I use it ? see what I tried in edited question please — François Richard, Mar 12 '22 at 16:20

Erwin Brandstetter · Accepted Answer · 2022-03-12T16:36:55.113

Basic conversion as instructed here:

What kind of datestyle can this be?

Repeat the same expression in GROUP BY, or use a simple positional reference, like:

SELECT date_trunc('day', to_timestamp(transaction_timestamp / 1000000000.0))
     , count(*)
FROM   transaction
GROUP  BY 1;

Be aware that to_timestamp() assumes UTC time zone for the given epoch to produce a timestamp with time zone (timestamptz). The following date_trunc() then uses the timezone setting of your current session to determine where to truncate "days". You may want to define a certain time zone explicitly ...
Basics:

Ignoring time zones altogether in Rails and PostgreSQL

Typically, it's best to work with a proper timestamptz to begin with. Unfortunately, Postgres timestamps only offer microsecond resolution. Since you need nanoseconds, your approach seems justified.

Group by day from nanosecond timestamp

1 Answers1