CODEWITHSUNDEEP
sqlpostgresql
jitendra varshney
jitendra varshney

Reputation: 3562

select query to select closest date which is less then or equal to current date in postgresql

this is my table , name is resource_calendar.

enter image description here

i want to select resource_id which have effective date less then or equal to current date and most closest date to current date.

what will be the right query in postgresql?

query will 

select effective date  22 for resource_id=3 and effective date  21 for resource_id=7

so result should be

  id      resource_id  calendar_id   applied_on               effective_date  version
  19       3            6            2016-12-22 11:13:26.53   2016-12-22      0
  26       7            5            2016-12-22 11:16:26.53   2016-12-21      0

Upvotes: 1

Views: 2853

Answers (2)

Durgpal Singh
Durgpal Singh

Reputation: 11983

You can use this. A simple query

SELECT DISTINCT ON(resource_id) *
FROM planner.resource_calendar 
WHERE effective_date <= CURRENT_DATE 
ORDER BY resource_id, effective_date desc;

Upvotes: 2

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522817

SELECT t.*
FROM
(
    SELECT id, resource_id, calendar_id, applied_on, effective_date, version,
           MIN(ABS(EXTRACT(EPOCH FROM (current_timestamp - effective_date))))
               OVER (PARTITION BY resource_id) AS diff
    FROM resource_calendar
    WHERE EXTRACT(EPOCH FROM (current_timestamp - effective_date)) > 0
) t
WHERE ABS(EXTRACT(EPOCH FROM (current_timestamp - t.effective_date))) = t.diff

This query forms a partition by resource_id on the resource_calendar. You can think of this partition as a logically grouping records together which have the same resource_id. For each such group of records, it computes the smallest difference between the effective_date and the current timestamp, where the effective_date be earlier than the current timestamp.

The outer query then identifies those records having this minimum timestamp difference.

Postgres has some reasonably helpful documentation on using window functions if you feel you need more information.

Upvotes: 2

Related Questions