Is 'YYYYQ' a valid DATETIME format for SQL? And if so, how do I make it with my data?

user_7535408 0
Viewed 285

I have some tables in a postgres that have a column for year and a column for quarter (both stored as bigint). I need to be able to combine those together in the output of a query in the form of 'YYYYQ' (not the hard part) AND have the datatype of that field be datetime (<--the hard part).

The only query I have attempted that didn't fail was -

SELECT to_date((year::VARCHAR + quarter::VARCHAR),'YYYYQ') AS Stuff 
FROM company.products

And while the output is in DATETIME format, there is no Quarter info in it.

Sample -

stuff
2011-01-01
2011-01-01
2012-01-01
2012-01-01
2012-01-01

Is it even possible to create output that has the format 'YYYYQ' AND is in DATETIME format? And if so, how?

datetime postgresql sql
2 Answers

You can save the date of the 1st day of the quarter. Multiply the recorded quarter -1 by 3.

SELECT to_date('2021','YYYY') + interval '6 month';
      ?column?
---------------------
 2021-07-01 00:00:00

SELECT to_char(to_date('2021','YYYY') + interval '6 month','YYYYQ');
 to_char
---------
 20213


SELECT q, 
 to_char(to_date('2021','YYYY') + interval '3 month'*(q-1),'YYYYQ') as YYYYQ,
 to_date('2021','YYYY') + interval '3 month'*(q-1) as d
FROM generate_series(1,4) f(q);

 q | yyyyq |          d
---+-------+---------------------
 1 | 20211 | 2021-01-01 00:00:00
 2 | 20212 | 2021-04-01 00:00:00
 3 | 20213 | 2021-07-01 00:00:00
 4 | 20214 | 2021-10-01 00:00:00
user_76355690

From the PostgreSQL docs (emphasis mine):

In to_timestamp and to_date, weekday names or numbers (DAY, D, and related field types) are accepted but are ignored for purposes of computing the result. The same is true for quarter (Q) fields.

user_6348240
Related