CODEWITHSUNDEEP
postgresqldatedateinterval
Shohrat Permanov
Shohrat Permanov

Reputation: 27

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

Upvotes: 0

Views: 1335

Answers (2)

Belayer
Belayer

Reputation: 14861

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'

Upvotes: 1

jawsem
jawsem

Reputation: 771

I am a little confused here. Why couldn't you just use the last query you posted? Does it not work? Do you just need to convert the last_update_time to a date?

Upvotes: 0

Related Questions