Inserting multiple dates in PostgreSQL

Question

Hi I would like to ask if it's possible to input dates starting from 01-01-2020 to 04-15-2020. Currently I have this code to iterate or to keep looping for each day from 00:00:00 to 23:00:00.

    INSERT INTO dashboard.availability(terminal_id, availability_date, availability_time)
    select td.terminal_id, td.down_date, (hh * interval '1 hour')::time
     from (select distinct terminal_id, down_date
      from dashboard.down_event
     ) td cross join
     generate_series(0, 23) gs(hh);

But can i also do it the same for the date looping each day until the date reached 2020-04-15?

For example:

terminal_id            availability_time     availability_date
   _____________     ___________________   _____________________
    012345678             00:00:00             2020-01-01
    012345678             01:00:00             2020-01-01
    012345678             02:00:00             2020-01-01
    012345678             03:00:00             2020-01-01
.
.
.  
   012345678             00:00:00               2020-04-15

GMB · Accepted Answer · 2020-05-03 18:09:47Z

2

Is this what you want?

insert into dashboard.availability(terminal_id, availability_date, availability_time)
select t.terminal_id, x.ts::date, x.ts::time
from (select distinct terminal_id from dashboard.down_event) t
cross join generate_series(date '2020-01-01', date '2020-04-15', interval '1 hour') x(ts)

For each terminal_id in table dashboard.down_event, this generates one row per hour between the January 1st and April 15th. The results are inserted into the target table, while keeping the date and time components in separate columns.

Side note: storing the data and time componnents of a timestamp in different columns is usually not a good idea; it makes things more complicated and less efficient when you actually need to access the timestamp value (which does happen, sooner or later).

answered May 3, 2020 at 18:09

GMB

224k25 gold badges103 silver badges151 bronze badges

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

3 Comments

Elijah Leis Over a year ago

Thanks! Noted on your side note!

Elijah Leis Over a year ago

Hi! I've looked the date for '2020-04-15' it only goes for 00:00:00 not 23:00:00

GMB Over a year ago

@ElijahLeis: right, so: generate_series(date '2020-01-01', date '2020-04-16', interval '1 hour').

Gordon Linoff · Accepted Answer · 2020-05-03 18:06:29Z

2

Why not just iterate by hour over the entire timeperiod?

select td.terminal_id, gs.down_date
from (select distinct terminal_id
      from dashboard.down_event
     ) td cross join
     generate_series('2020-01-01'::timestamp, '2020-04-15'::timestamp, interval '1 hour) gs(down_date);

answered May 3, 2020 at 18:06

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

2 Comments

Elijah Leis Over a year ago

How about the time? The query above only gets 2 columns. But I need to insert 3 SELECTS

Gordon Linoff Over a year ago

@ElijahLeis . . . The second column has both the date and the time. That seems like a feature, not a bug, but you can obvious extract the date and time values from it.

Collectives™ on Stack Overflow

Inserting multiple dates in PostgreSQL

2 Answers 2

3 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related