CODEWITHSUNDEEP
postgresqlsql-timestamp
Tomás Gomes
Tomás Gomes

Reputation: 45

PostgreSQL grouping timestamp by day

I have a table x(x_id, ts), where ts is a timestamp. And I have a second table y(y_id, day, month, year), which is supposed to have its values from x(ts). (Both x_id and y_id are serial) For example:

            x                                  y

_x_id_|__________ts__________        _y_id_|_day_|_month_|__year__
  1   | '2019-10-17 09:10:08'          1     17     10      2019
  2   | '2019-01-26 11:12:02'          2     26      1      2019

However, if on x I have 2 timestamps on the same day but different hour, this how both tables should look like: 

            x                                  y

_x_id_|__________ts__________        _y_id_|_day_|_month_|__year__
  1   | '2019-10-17 09:10:08'          1     17     10      2019
  2   | '2019-10-17 11:12:02'

Meaning y can't have 2 rows with the same day, month and year. Currently, the way I'm doing this is:

INSERT INTO y(day, month, year)
SELECT
EXTRACT(day FROM ts) AS day,
EXTRACT(month FROM ts) AS month,
EXTRACT(year FROM ts) AS year
FROM x
ORDER BY year, month, day;

However, as you probably know, this doesn't check if the timestamps share the same date, so how can I do that? Thank you for your time!

Upvotes: 1

Views: 131

Answers (2)

Matthieu
Matthieu

Reputation: 3097

Add a UNIQUE constraint on table y to prevent adding the same date twice.

CREATE UNIQUE INDEX CONCURRENTLY y_date 
ON y (year,month,day)

Then add it to y:

ALTER TABLE y
ADD CONSTRAINT y_unique_date
UNIQUE USING INDEX y_date

Note that you'll get an SQL error when the constraint is violated. If you don't want that and just ignore the INSERT, use a BEFORE INSERT trigger, returning NULL when you detect the "date" already exists, or just use ON CONFLICT DO NOTHING in your INSERT statement, as hinted by @Belayer.

Upvotes: 1

Belayer
Belayer

Reputation: 14861

Assuming you build the unique index as recommended above change your insert to:

insert into y(day, month, year)
  select extract(day from ts) as day,
       , extract(month from ts) as month,
       , extract(year from ts) as year
    from x
    on conflict do nothing;

I hope your table X is not very large as the above insert (like your original) will attempt inserting a row into Y for every row in X on every execution - NO WHERE clause.

Upvotes: 2

Related Questions