How to do Query in Postgres SQL based on time [duplicate]

Question

I would like to do a query in postgres which should bring count of records that updated previous one hour

For example 9AM-10AM records updated is 100 means I should get 100. 
            10AM-11AM records update is 200 means I should get 200(not 300).

Sample time stamp in updated column 2011-02-03 09:00:00 I have tried like this

select count(*) from customer where updated>=now()-1 and updated<now()

I know now()-1 will take yesterday. I dont know how to minus one hour from now

This is what i tried. But i have no idea how to mix with my querySELECT EXTRACT(hOUR FROM TIMESTAMP '2001-02-16 20:38:40'). My query is select count(*) from cusotmer where updated='' — Kathirvel Appusamy
– Kathirvel Appusamy, Commented Jan 6, 2015 at 16:54
Please show sample data and expected results, ideally as a SQL Fiddle. — Neville Kuyt
– Neville Kuyt, Commented Jan 6, 2015 at 17:04

fvu · Accepted Answer · 2015-01-06 17:17:22Z

0

where updated between 
  (date_trunc('hour',current_timestamp) - interval '1 hour') 
  and 
  (date_trunc('hour',current_timestamp))

Explanation:

date_trunc('hour',current_timestamp)

Gives the current time with minutes, seconds and milliseconds set to 0, and

 - interval '1 hour'

subtracts one hour from a given timestamp.

Combine those to create the range you want ( between )

answered Jan 6, 2015 at 17:17

fvu

33.1k6 gold badges64 silver badges84 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

I will check and get back to you if I face any issue. I think It will work