CODEWITHSUNDEEP
sqlpostgresql
user12561026
user12561026

Reputation:

Get the dates of two weeks from today from database

I have some dates in postgresql database. I want to find dates from today to next two weeks or 14 days. How i can find the dates between current date and next 14 days? This query is not working. I have date format 2019-12-26 in database.

"SELECT work_date FROM USERS_SCHEDULE WHERE user_id = 11 AND data(now() +14)";

Upvotes: 0

Views: 349

Answers (2)

Jim Jones
Jim Jones

Reputation: 19623

Simply by adding the number of days to the date you can set the limit date you want.

Sample Data

CREATE TABLE users_schedule (work_date DATE);
INSERT INTO users_schedule 
SELECT generate_series(CURRENT_DATE, DATE '2020-01-31', '1 day');

Query (dates between the current date and 3 days later)

SELECT work_date FROM users_schedule 
WHERE work_date BETWEEN CURRENT_DATE AND CURRENT_DATE + 3;
 work_date  
------------
 2019-12-26
 2019-12-27
 2019-12-28
 2019-12-29
(4 rows)

If you mean you want to get all possible dates inside an interval, take a look at generate_series:

SELECT generate_series(DATE '2016-08-01', DATE '2016-08-14', '1 day'); 

    generate_series     
------------------------
 2016-08-01 00:00:00+02
 2016-08-02 00:00:00+02
 2016-08-03 00:00:00+02
 2016-08-04 00:00:00+02
 2016-08-05 00:00:00+02
 2016-08-06 00:00:00+02
 2016-08-07 00:00:00+02
 2016-08-08 00:00:00+02
 2016-08-09 00:00:00+02
 2016-08-10 00:00:00+02
 2016-08-11 00:00:00+02
 2016-08-12 00:00:00+02
 2016-08-13 00:00:00+02
 2016-08-14 00:00:00+02
(14 rows)

Using CURRENT_DATE

SELECT generate_series(CURRENT_DATE, DATE '2019-12-31', '1 day'); 
    generate_series     
------------------------
 2019-12-26 00:00:00+01
 2019-12-27 00:00:00+01
 2019-12-28 00:00:00+01
 2019-12-29 00:00:00+01
 2019-12-30 00:00:00+01
 2019-12-31 00:00:00+01
(6 rows)

Upvotes: 1

Akina
Akina

Reputation: 42632

SELECT work_date 
FROM users_schedule 
WHERE user_id = 11 
  AND work_date BETWEEN CURRENT_DATE
                    AND CURRENT_DATE + INTERVAL '14 days'

Upvotes: 0

Related Questions