Extracting year after converting to a date having issues in Postgresql

Question

I have a column which is of String data type but holds a date value. I converted it to a DATE and then extracted the YEAR from it using the below format in POSTGRESQL. However, if I try to get the last 10 year data, I see that any date that is lesser than the last 50 years ( i.e less than 1970 , takes up the format of 2069, 2068 and so on). Lets say there is a date value in the format (10/1/58, it gets listed inspite of giving the filter for the last 10 years as 2058)

I used the below for converting to DATE and extracting the year

extract YEAR FROM  TO_DATE(release_date,'MM/DD/YY')

Then used the below condition in my query to get the last 10 year data

 where  extract(year from current_timestamp)-EXTRACT(YEAR FROM  TO_DATE(release_date,'MM/DD/YY')) <=10

How to solve this in POSTGRESQL

Use 'MM/DD/YYYY instead of 'MM/DD/YY'. Per docs here Data formatting: 'In to_timestamp and to_date, if the year format specification is less than four digits, e.g., YYY, and the supplied year is less than four digits, the year will be adjusted to be nearest to the year 2020, e.g., 95 becomes 1995.' — Adrian Klaver
– Adrian Klaver, Commented Aug 25, 2021 at 21:56
I dont have problems with 1995 . Its the years less than 1970 .Year 12/12/69 shows up as 2069 . I tried using MM/DD/YYYY but see only 69 being returned — NK1
– NK1, Commented Aug 25, 2021 at 22:03
What a bad idea to store a date as a string. And with only two digits for the year even. You cannot expect the DBMS to guess the century right. It's not a human being who knows its 1843 when told that Dickens wrote A Christmas Carol in '43. So you must adjust the DBMS's guesses. As it seems you want it to be in the past 100 years until now, use CASE WHEN on the year and subtract 100 years when necessary. (And then you should change the column's data type to DATE in order to avoid such problems in the future.) — Thorsten Kettner
– Thorsten Kettner, Commented Aug 25, 2021 at 22:08
What you are facing was the solution to the Y2K problem (22 ago). Some 2digit year was selected as a pivotal year, for Postgres looks like that value now stands at 70. Any value less that that is the prior century, any number equal or greater the current century. Solution: as @AdrianKlaver suggested use a 4digit year, or the ISO Standard format DATE yyyy-mm-dd (ecample 1DATE '2072-01-01'` — Belayer
– Belayer, Commented Aug 25, 2021 at 22:20

Adrian Klaver · Accepted Answer · 2021-08-25 22:27:35Z

1

Well:

select extract(year from current_timestamp);
date_part 
-----------
      2021

select EXTRACT(YEAR FROM  TO_DATE('10/1/58','MM/DD/YYYY'));
 date_part 
-----------
        58

select 2021 - 58;
 ?column? 
----------
     1963

1963 ! <= 10

Your best bet would be to clean up the date data by turning YY into YYYYand better yet by storing in adate` field instead of as a string.

edited Aug 25, 2021 at 22:27

answered Aug 25, 2021 at 22:16

Adrian Klaver

20.4k3 gold badges24 silver badges40 bronze badges

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

Comments

nbk · Accepted Answer · 2021-08-25 22:30:53Z

1

As this is documented behaviour

use a function to determine the year

CREATE TABLE mytable(release_date varchar(20))

INSERT INTO mytable VALUES ('01/01/69'),('01/01/70')

CREATE OR REPLACE FUNCTION corectyear(inyear integer) RETURNS integer AS $$
DECLARE curyear INTEGER;
        BEGIN
        SELECT extract( YEAR FROM  CURRENT_DATE) INTO curyear;
        IF inyear > curyear THEN
          RETURN inyear - 100;
        ELSE
           RETURN inyear;
        END IF;
                
        END;
$$ LANGUAGE plpgsql;

SELECt corectyear(extract( YEAR FROM  TO_DATE(release_date,'MM/DD/YY'))::INTEGER) FROM mytable

| corectyear |
| ---------: |
|       1969 |
|       1970 |

db<>fiddle here

answered Aug 25, 2021 at 22:30

nbk

49.6k8 gold badges32 silver badges53 bronze badges

Collectives™ on Stack Overflow

Extracting year after converting to a date having issues in Postgresql

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related