Storing an array of dates (without times) in PostgreSQL

0
votes

I'm trying to store dates (without times) in a PostgreSQL array.

For context, let's say these are dates on which a supermarket will be closed:

['2022-12-24', '2022-12-25', '2022-12-26']

The table is called opening_times and there is a column called closed_days which has a type of date[]:

table.specificType('closed_days', 'date[]').defaultTo([]).notNullable()

However, when I UPDATE this field using SET closed_days = '{"2022-10-16"}', it seems PostgreSQL is converting it into a full ISO date and time string like this: ["2022-10-15T23:00:00.000Z"]

According to the PostgreSQL docs (section 8.5), the date type is supposed to have a resolution of 1 day, but somehow it is still storing the time. Elsewhere in the database, fields of type date do indeed have a granularity of 1 day (time is not stored), but in these instances I am not using an array.

--

Additional information

  • I am running PostgreSQL 14.2 inside a Docker container (psql is also running inside the container)
  • The type of the column in Beekeeper Studio shows as _date, but you can see the ORM code above that was used to create the field using type date[], so I assume _date is just another notation for the same.
  • In psql, running \d opening_times shows that the column has a type of date[].
  • The result of select array['2022-07-28'::date] is ["2022-07-27T23:00:00.000Z"] when run in Beekeeper Studio. When the same query is run in psql I get {2022-07-28}.
postgresqldate
select array['2022-07-28'::date]; {07/28/2022}. What client are you using? What is the closed_days type in the database(my bet is timestamp(tz)[])? Add answers as update to question. – Adrian Klaver
@AdrianKlaver Thanks for your comment. I'm using Beekeeper Studio but it also seems to happen using Lucid ORM. The type in Beekeeper Studio shows as _date, but you can see the ORM code above that was used to create the field using type date[], so I assume _date is just another notation for the same. Could you explain the purpose of the SELECT query? I'll update the question. – Obvious_Grapefruit
What is the data type as shown in the database itself? In psql do \d <table_name> to find it. The select shows that an array of dates will be date[] or it's alias _date vs select array['2022-07-28'::date]::timestamptz[]; {"07/28/2022 00:00:00 PDT"} which I believe is what is happening. The date[] is updating a timestamptz[]. – Adrian Klaver
@AdrianKlaver Oh you must have one of these weird locales, not the default ISO datestyle :-) dbfiddle.uk/… – Bergi
@AdrianKlaver In psql the type shows as date[]. I ran the SELECT query and the result was ["2022-07-27T23:00:00.000Z"]. – Obvious_Grapefruit