CODEWITHSUNDEEP
mysqlsql
Jecki
Jecki

Reputation: 802

time period SQL count query

i;m trying to count elements for some period of time using the following query 

select count(*) 
from push_message 
where app_names='AP1' AND
      time < (current_timestamp - interval '30 day')

I still get the whole count and not for 30 days , my "time" column will be like 

time               | 2019-08-22 17:59:02.686915+04

what did i missed on my query ? please advise

Upvotes: 1

Views: 42

Answers (1)

CompEng
CompEng

Reputation: 7376

try this:

select 
  *
from
  yourtable t
where
  /* Greater or equal to the start of last month */
  t.date >= DATE_ADD(LAST_DAY(DATE_SUB(NOW(), INTERVAL 2 MONTH)), INTERVAL 1 DAY) and
  /* Smaller or equal than one month ago */
  t.date <= DATE_SUB(NOW(), INTERVAL 1 MONTH)

Upvotes: 1

Related Questions