PostgreSQL grouping timestamp by day

Question

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!

You can [add a unique constraint](http://www.postgresqltutorial.com/postgresql-unique-constraint/) on fields (year, month, day) to prevent adding such "duplicates" into table y. — Matthieu, Dec 13 '19 at 02:29
But if I do that, when I try insert the values, I get the error of unique constraint and my y table ends up empty. — Tomás Gomes, Dec 13 '19 at 02:36
Are you inserting several values in a single transaction? You get an error when the constraint is violated but the first values will get inserted (because the constraint is valid at least for the first one). — Matthieu, Dec 13 '19 at 02:42

score 2 · Answer 1 · answered Dec 13 '19 at 06:10

2

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.

answered Dec 13 '19 at 06:10

Belayer

13,578
2
11
22

I didn't know about the `on conflict do nothing`. Thanks! – Matthieu Dec 13 '19 at 06:37
And the reason why his table is empty is probably because there is no where-clause. – Matthieu Dec 13 '19 at 06:39
Thank you, I tried as you said, but now I'm getting a syntax error. Does this work for PostgreSQL? – Tomás Gomes Dec 13 '19 at 11:29
AFAIK this **works only** in PostgreSQL What exactly did you run? What exactly did you get? – Belayer Dec 13 '19 at 16:20

Matthieu · Accepted Answer · 2019-12-13T10:56:56.697

1

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.

edited Dec 13 '19 at 10:56

answered Dec 13 '19 at 02:40

Matthieu

2,736
4
57
87

This worked (the trigger solution)! Thank you very much! – Tomás Gomes Dec 13 '19 at 12:10

PostgreSQL grouping timestamp by day

2 Answers2