Obtaining summary result since last occurence of specifc hour of day in postgresql

I have rainfall measurements stored in a postgresql table and wish to select the min and and max value since the last occurence of 9am, whether that is 9am of the current day, if after 9am, or 9am of the previous day if before 9am. Have managed to select the values I need for the current day like so: "select max(rain_mm) as maxrain,min(rain_mm) as minrain from weatherstation_000 where time_stamp > date_trunc('day',now());" But now want a simple way to do min and max rainfall since 9am as well, whether that be through the use of a conditional expression or perhaps a change of timezone??

Answer

You can use the OR condition as follows based of now().

select max(rain_mm) as maxrain,
       min(rain_mm) as minrain
  from weatherstation_000
 where (now() > date_trunc('day', now()) + interval '9 hour' 
         and time_stamp > date_trunc('day', now()) + interval '9 hour')
    OR ((now() <= date_trunc('day', now()) + interval '9 hour'
         and time_stamp > date_trunc('day', now()) - interval '15 hour'))