CODEWITHSUNDEEP
postgresql
Linu
Linu

Reputation: 597

Selecting certain columns from a table with dates as columns

I have a table where column names are like years "2020-05","2020-06", "2020-07" etc and so many years as columns.I need to select only the current month, next month and third month columns alone from this table.(DB : PostgreSQL Version 11)

But since the column names are "TEXT" are in the format YYYY-MM , How can I select only the current month and future 2 months from this table without hard-coding the column names.

Below is the table structure , Name : static_data enter image description here

Required select statement is like this,The table contains the 14 months data as in the above screen shot like DATES as columns.From this i want the current month , and next 2 month columns along with their data, something like below.

SELECT "2020-05","2020-06","2020-07" from static -- SELECT Current month and next 2 months Required output:

enter image description here

Upvotes: 1

Views: 919

Answers (3)

user330315
user330315

Reputation:

It's nearly impossible to get the actual value of the current month as the column name, but you can do something like this:

select d.item_sku,
       d.status,
       to_jsonb(d) ->> to_char(current_date, 'yyyy-mm') as current_month,
       to_jsonb(d) ->> to_char(current_date + interval '1 month', 'yyyy-mm') as "month + 1",
       to_jsonb(d) ->> to_char(current_date + interval '2 month', 'yyyy-mm') as "month + 2"
from bad_design d
;

Upvotes: 2

GMB
GMB

Reputation: 222432

Disclaimer: this does not answer your question - but it's too long for a comment.

You need to fix the design of this table. Instead of storing dates in columns, you should have each date on a separate row.

There are numerous drawbacks to your current design:

  • very simple queries are utterly complicated : filtering on dates, aggregation... All these operations require dynamic SQL, which adds a great deal of complexity

  • adding or removing new dates requires modifying the structure of the table

  • storage is wasted for rows where not all columns are filled

Instead, consider this simple design, with one table that stores the master data of each item_sku, and a child table 

create table myskus (
    item_sku int primary key,
    name text,
    cat_level_3_name text
);

create table myvalues (
    item_sku int references myskus(item_sku),
    date_sku date,
    value_sku text,
    primary key (item_sku, date_sku)
);

Now your original question is easy to solve:

select v.*, s.name, s.cat_level_3_name
from myskus s
inner join myvalues v on v.item_sku = s.item_sku
where 
    v.date_sku >= date_trunc('month', now()) 
    and v.date_sku < date_trunc('month', now()) + interval '3 month'

Upvotes: 0

edd
edd

Reputation: 1417

Technically, you can use the information schema to achieve this. But, like GMB said, please re-design your schema and do not approach this issue like this, in the first place.

The special schema information_schema contains meta-data about your DB. Among these is are details about existing columns. In other words, you can query it and convert their names into dates to compare them to what you need.
Here are a few hints.

Query existing column names.

SELECT column_name
FROM information_schema.columns
WHERE table_schema = 'your_schema'
  AND table_name   = 'your_table'

Compare two dates.

SELECT now() + INTERVAL '3 months' < now() AS compare;

 compare                                                       
---------
 f
(1 row)

You're already pretty close with the conversion yourself.

Have fun and re-design your schema!

Upvotes: 0

Related Questions