PostgreSQL: display remaining days-CodePudding

I have a table containing list of trainees and I have to find out how many days are left until the end of their practice. The field contains practice start date, and the output should contain a field like "Days(x)". The trick is to display only those trainees who are engaged in training (8 months). Test values for the table:

CREATE TABLE IF NOT EXISTS "interns" (
  "intern_id" smallint(5) NOT NULL,
  "f_name" varchar(20) NOT NULL,
  "l_name" varchar(20) NOT NULL,
  "gender" varchar(20) NOT NULL,
  "pr_st_date" date DEFAULT NULL,
  PRIMARY KEY ("intern_id");

INSERT INTO scheme.interns VALUES 
(1,'Ivan','Ivanov','M','2020-09-22'),
(2,'Natalia','Kurtz','F','2021-01-12'),
(3,'Kristian','Tompson','M','2021-10-20'),
(4,'Libi','Uolsh','F','2021-12-30'),
(5,'Garry','Cutcher','M','2021-01-30'),
(6,'Steven','Larson','M','2021-02-25'),
(7,'Hugh','Laurie','M','2021-03-16'),
(8,'Jonny','Walker','M','2021-01-31'),
(9,'Ketty','Perri','F','2021-05-11'),
(10,'Liza','Torn','F','2020-11-28'),
(11,'Ely','Buggle','F','2021-01-16');

I have no idea how to separate negative values for AGE(CURRENT_DATE, "pr_st_date") from positive ones, I tried to use this function to filter those interns who haven't started their practice. Googled almost everything and just got stuck. PS: I've just started to learn SQL, so sorry about that.

CodePudding user response：

Interns who haven't started their practice could be filtered out using "pr_st_date" < CURRENT_DATE as their start date would be greater than the current date.

You may try the following:

The first query shows the logic/result of the calculations while the final query may be what you're looking for. The date_part function was used here

Query #1

SELECT
    *,  
    AGE("pr_st_date") as age,
    DATE_PART('year',  AGE("pr_st_date")) as years,
    DATE_PART('month',AGE("pr_st_date")) as months,
    CURRENT_DATE,
    CASE
        WHEN "pr_st_date" < CURRENT_DATE AND 
              DATE_PART('year',  AGE("pr_st_date")) < 1 AND
              DATE_PART('month',AGE("pr_st_date")) <8 THEN 1
        ELSE 0
    END as in_training,
    (CURRENT_DATE - "pr_st_date") as days_remaining
FROM
    interns;

intern_id	f_name	l_name	gender	pr_st_date	age	years	months	current_date	in_training	days_remaining
1	Ivan	Ivanov	M	2020-09-22T00:00:00.000Z	{"years":1,"days":22}	1	0	2021-10-14T00:00:00.000Z	0	387
2	Natalia	Kurtz	F	2021-01-12T00:00:00.000Z	{"months":9,"days":2}	0	9	2021-10-14T00:00:00.000Z	0	275
3	Kristian	Tompson	M	2021-10-20T00:00:00.000Z	{"days":-6}	0	0	2021-10-14T00:00:00.000Z	0	-6
4	Libi	Uolsh	F	2021-12-30T00:00:00.000Z	{"months":-2,"days":-16}	0	-2	2021-10-14T00:00:00.000Z	0	-77
5	Garry	Cutcher	M	2021-01-30T00:00:00.000Z	{"months":8,"days":15}	0	8	2021-10-14T00:00:00.000Z	0	257
6	Steven	Larson	M	2021-02-25T00:00:00.000Z	{"months":7,"days":17}	0	7	2021-10-14T00:00:00.000Z	1	231
7	Hugh	Laurie	M	2021-03-16T00:00:00.000Z	{"months":6,"days":29}	0	6	2021-10-14T00:00:00.000Z	1	212
8	Jonny	Walker	M	2021-01-31T00:00:00.000Z	{"months":8,"days":14}	0	8	2021-10-14T00:00:00.000Z	0	256
9	Ketty	Perri	F	2021-05-11T00:00:00.000Z	{"months":5,"days":3}	0	5	2021-10-14T00:00:00.000Z	1	156
10	Liza	Torn	F	2020-11-28T00:00:00.000Z	{"months":10,"days":16}	1	10	2021-10-14T00:00:00.000Z	0	320
11	Ely	Buggle	F	2021-01-16T00:00:00.000Z	{"months":8,"days":29}	0	8	2021-10-14T00:00:00.000Z	0	271
12	Bob	Builder	M	2021-10-01T00:00:00.000Z	{"days":13}	0	0	2021-10-14T00:00:00.000Z	1	13

Query #2

SELECT
    *,
    (CURRENT_DATE - "pr_st_date") as days_remaining
FROM
    interns
WHERE
    "pr_st_date" < CURRENT_DATE AND 
     DATE_PART('year',  AGE("pr_st_date"))< 1 AND 
     DATE_PART('month',AGE("pr_st_date")) <8;

intern_id	f_name	l_name	gender	pr_st_date	days_remaining
6	Steven	Larson	M	2021-02-25T00:00:00.000Z	231
7	Hugh	Laurie	M	2021-03-16T00:00:00.000Z	212
9	Ketty	Perri	F	2021-05-11T00:00:00.000Z	156
12	Bob	Builder	M	2021-10-01T00:00:00.000Z	13

View working demo on DB Fiddle

Let me know if this works for you.