Select comparing current date with exact date 1 month ago

Question

Select comparing current date with exact date 1 month ago

Navigation

#1 by (0 votes)
#2 by (0 votes)

0

I made this select that returns me the amount of records in the last 24h

select count(*) from registros 
where registry_date >= NOW() - interval '24 hours'

I need a select that compares 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) there were 90 records. I need to compare these 90 records with the amount of records on 09/10/2018

sql postgresql

asked by anonymous 10.10.2018 / 16:34

2 answers

Parallel Execution Reading XML and Printing Data in Word or PDF with Python

score 0 · Answer 1

So I understand you need the totals per day, total type 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 get some of the total on day 10 and part of day 09).

Try 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')

score 0 · Answer 2

If I understand correctly you need the two values in the same query, I think it would look something like this, if 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'