May 2018
Intermediate to advanced
576 pages
30h 25m
English
Moving from the integer to the date type uses a complex USING expression. Let's break that down step by step so that we can see why, as follows:
postgres=# ALTER TABLE birthdayALTER COLUMN dob SET DATA TYPE dateUSING date(to_date(dob::text, 'YYMMDD') - (CASE WHEN dob/10000 BETWEEN 16 AND 69 THEN interval '100 years' ELSE interval '0' END));
First, we can't move directly from integer to date. We need to convert it to text and then to date. The dob::text statement means cast to text.
Once we have text, we use the to_date() function to move to a date type.
This is not enough; our starting data was 690926, which we presume is a date in the YYMMDD format. When PostgreSQL converts this data to a date, it assumes that the two-digit ...