Search code examples
postgresqldatedateinterval

How I can set interval between two column with date and time in PostgreSQL?

I want to set day or hour time interval between 2 columns to get lates actual records, if I dont set this I got outdated information when I filter by day.

TableA: create_date TableB: last_access_date

SO I did this 

SELECT  *
FROM acc_zone_person
WHERE create_time::date = last_access_time::date

I get daily information no outdated information, but at midnight all information between 23:00 - 00:00 disgarded I want to put interval so that my evening information from 19:00 will be present till 06:00 data.

I used 

SELECT  *
FROM acc_zone_person
WHERE last_access_time::date >= now() - interval '12 hours'

this time when I switch to older dates, I dont get any data, only data within 12 hours

so I need to find a way do something like this

SELECT  *
FROM acc_zone_person
WHERE create_date::date >= last_access_time - interval '12 hours'

It should take create_date as NOW and get 12 hours interval data of last_access_time

Solution

  • According to your own statement: "It should take create_date as NOW ... "

    SELECT  *
FROM acc_zone_person
WHERE now()::date >= last_access_time - interval '12 hours'