0
I made this select that returns me the amount of records in the last 24 hours
select count(*) from registros
where registry_date >= NOW() - interval '24 hours'
I need a select to compare the value that is returned in this first query with the value of the same date in the previous month.
Example: Today (10/10/2018) were 90 records. I need to compare these 90 records with the number of records of the day 10/09/2018
0
From what I understand you need the totals per day, type total day 10/10/2018, total day 09/10/2018; that is, the comparison should be made by the day instead of -24h (this way you take part of the total day 10 and part of 09).
Try it like this:
-- total de hoje
select count(*) from registros
where to_char(registry_date, 'DD-MON-YYYY') = to_char(NOW(), 'DD-MON-YYYY')
-- total um mês atrás
select count(*) from registros
where to_char(registry_date, 'DD-MON-YYYY') = to_char((NOW() - interval '1 month'), 'DD-MON-YYYY')
0
Well if I understand correctly you need the two values in the same query, I believe it would look something like this, in case your 'Registry date' field is in date format you will need to group by date.
select count(*) from registros
where
registry_date >= NOW() - interval '24 hours'
or
registry_date = NOW() - interval '1 month'
Browser other questions tagged sql postgresql
You are not signed in. Login or sign up in order to post.
CURRENT_DATE - interval '1 Month' provides the date of the previous month.
– anonimo
@Programmer takes a look at my answer..
– rLinhares
Do you need to return the two values in the same query or just one? Ex: return two lines one with the date 10/10/2018 and 90 records and the other 10/09/2018 and the amount of records?
– Wel