Select data between dates and between times of day

Question

I want to query rows for a given time range and also filter between given times of day.

Example I want to filter for times of day between '9.00 AM' and '10.00 PM' of every date within a given time range.

My table looks like this.

This is my sample code:

SELECT * 
FROM public.energy 
WHERE time >= date_trunc('month', NOW() - INTERVAL '1 MONTH') AT TIME ZONE 'Asia/Bangkok'
AND time < date_trunc('MINUTE', NOW()- INTERVAL '1 MONTH') AT TIME ZONE 'Asia/Bangkok'
AND name = 'SWU0001'
ORDER BY id DESC;

I already select data between dates that I want, but I want to filter for specific times.

Answer 1

SELECT * 
FROM   public.energy 
WHERE  name = 'SWU0001'
AND    time >= date_trunc('month' , now() AT TIME ZONE 'Asia/Bangkok' - interval '1 month') AT TIME ZONE 'Asia/Bangkok' -- !
AND    time <  date_trunc('minute', now() AT TIME ZONE 'Asia/Bangkok' - interval '1 month') AT TIME ZONE 'Asia/Bangkok' -- !
AND    (time AT TIME ZONE 'Asia/Bangkok')::time BETWEEN '09:00' AND '22:00'  -- !!!
ORDER  BY id DESC;

Don't call a timestamptz column "time". The name is misleading, and it's a basic type name.

Also, to work with local time of 'Asia/Bangkok' you need to get the local time for that time zone before applying date_trunc(), and cast the adjusted value back to timestamptz at the end. Basics:

• Ignoring time zones altogether in Rails and PostgreSQL