Difference between CURRENT_TIMESTAMP and CURRENT_DATE

Question

I want to get the data from the last 28 days and only include complete days. So what I mean is, when I look at the data today at 10:00 AM, it only includes data from yesterday (the completed day) and 28 days before yesterday.

I am creating a live dashboard with figures like this. So I don't want the numbers to change until the day is finished.

Also, I am willing to understand the difference between CURRENT_DATE and CURRENT_TIMESTAMP

For example, in my code, if I use CURRENT_TIMESTAMP, will I get the data from today 10:00 AM back to 28 days ago 10:00 AM? if not, how can I get data in a way numbers change live according to every time I run the code (the average time that data change in the database is 10 minutes).

My simplified code:

select count(id) from customers 
where created_at > CURRENT_DATE - interval '28 days'

Maybe I am using wrong code, can you please give me advice on how to get the date in both formats:

1. include only complete days(does not include today, until the day is finished)
2. include hours, from today morning until 28 days back same time in the morning.

Answer 1

Assuming created_at is of type timestamptz.

1. include only complete days(does not include today, until the day is finished)

Start with now() and use date_trunc():

SELECT count(*)
FROM   customers 
WHERE  created_at <  date_trunc('day', now())
AND    created_at >= date_trunc('day', now() - interval '28 days');

Or work with CURRENT_DATE ...

WHERE  created_at <  CURRENT_DATE
AND    created_at >= CURRENT_DATE - 28;

The result for both depends on the current timezone setting. The "date" functionally depends on your current time zone. The type timestamp with time zone (timestamptz) does not. But the expression date_trunc('day', now()) introduces the same dependency as the "day" is defined by your current time zone. So you need to define which "days" you mean precisely. Basics:

• Ignoring time zones altogether in Rails and PostgreSQL

You can subtract integer values from a date to subtract days:

• How do I determine the last day of the previous month using PostgreSQL?

now() is a shorter equivalent of CURRENT_TIMESTAMP. See:

• Difference between now() and current_timestamp

count(*) is equivalent to count(id) while id is defined NOT NULL, but a bit faster.

• I have different results from query for COUNT('e.id') or COUNT(e.id)

2. include hours, from today morning until 28 days back same time in the morning.

Simply:

WHERE  created_at > now() - interval '28 days'

No dependency on the current time zone.