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)

CodePudding user response：

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.