Minimum of Days Difference between Dates and Update PostgreSQL

Question

I have a situation like, I need to find out which record is having minimum difference of days between atleast 2 dates. Like -

Table Data is like

Input Parameter : [aDate = 12-Nov-2020, Infold = 2]

Result should be:

Need to find the date difference between the input dates and existing dates, find the record which is having minimum days gap.

I am trying in this way:

select/update query
where <here not sure what to put> = MIN ( select existing.aDate::date - input.aDate::date from table, select input.aDate::date - existing.aDate::date from table )

I just need to identify that particular record, kindly advise.

GMB · Accepted Answer · 2020-11-20 10:01:45Z

2

You can use order by and limit to identify the "closest" record to a given date:

select t.*
from mytable t
order by abs(adate - date '2020-11-12'))
limit 1

If you want an update statement:

update mytable t
set infold = 2
from (
    select pk 
    from mytable 
    order by abs(adate - date '2020-11-12'))
    limit 1
) t1
where t1.pk = t.pk

answered Nov 20, 2020 at 10:01

GMB

224k25 gold badges103 silver badges151 bronze badges

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

1 Comment

sys Over a year ago

Thanks GMB, I used the concept, and its working for me. Thanks a lot !

Collectives™ on Stack Overflow

Minimum of Days Difference between Dates and Update PostgreSQL

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related