I need to find in postgres number of rows from table history, where date in clock column is from previous month (clock is stored as epoch timestamp). First i did:
select count(clock) from history where
date_trunc('month',to_timestamp(clock)) = date_trunc('month', CURRENT_DATE) - INTERVAL '1 month';
But this is very slow. I was thinking i would be quicker if i did something like this:
select count(clock) from history
where clock between {extracted first second of previous month} and {extracted last second of previous month};
When i put values by hand, is much quicker (I have index on clock). But I don't know how extract first, and last second of the previous month. Thanks for help in advance :)
Start of the previous month as a timestamp is:
date_trunc('month', current_timestamp) - interval '1' month
If you avoid the between operator, you don't need calculate the "last second" of the previous month but only the start of the current month:
select count(clock)
from history
where clock >= extract(epoch from date_trunc('month', current_timestamp) - interval '1' month)
and clock < extract(epoch from date_trunc('month', current_timestamp));
Note the < operator instead of <=