0

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}. When I run it in Lucid ORM:
const db = await Database.rawQuery("select array['2022-07-28'::date]")
console.log(db.rows)

I get this: [ { array: [ 2022-07-27T23:00:00.000Z ] } ].

Bergi
  • 630,263
  • 148
  • 957
  • 1,375
Obvious_Grapefruit
  • 640
  • 1
  • 8
  • 21
  • `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 Jul 28 '22 at 23:39
  • @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 Jul 28 '22 at 23:45
  • What is the data type as shown in the database itself? In `psql` do `\d ` 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 Jul 29 '22 at 00:11
  • @AdrianKlaver Oh you must have one of these weird locales, not the default `ISO` datestyle :-) https://dbfiddle.uk/?rdbms=postgres_14&fiddle=fcf1b2baa6499f1459db970493713cda – Bergi Jul 29 '22 at 00:16
  • @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 Jul 29 '22 at 00:21
  • Add the information in your comments to the question so folks don't have to walk through the comments to get relevant information. – Adrian Klaver Jul 29 '22 at 00:35
  • @Obvious_Grapefruit Where did you run the query, also in `psql`? Or is that the result from your ORM? – Bergi Jul 29 '22 at 00:36
  • @Bergi, doesn't really change the examples: `set datestyle= 'ISO,MDY' ; select array['2022-07-28'::date]; {2022-07-28}`. – Adrian Klaver Jul 29 '22 at 00:36
  • @Bergi, I initially ran the query in Beekeeper Studio which gave an output of `["2022-07-27T23:00:00.000Z"]`. I just ran it again in `psql` and the result was different - `{2022-07-28}`. I'm running PostgreSQL (and `psql`) in a Docker container if that changes anything. @AdrianKlaver I've updated the question, thanks for reminding me. – Obvious_Grapefruit Jul 29 '22 at 00:50
  • @Obvious_Grapefruit Then apparently Beekeeper Studio is the fault, it does convert `date` values into its own representation. Looks a bit like a JavaScript `Date` in an array that is serialised to JSON. Not a problem with Postgres. Do you actually have code (using your ORM) that is failing, or were you only confused by the Beekeeper Studio output? – Bergi Jul 29 '22 at 01:14
  • @Bergi It happens in the ORM too, I updated the question with the output. – Obvious_Grapefruit Jul 29 '22 at 01:25
  • 1
    So this is JavaScript? And it's using the [node-postgres](https://node-postgres.com/) driver? Then the case is clear: that is converting the `date` string you're getting into a [`Date` object](https://developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Global_Objects/Date). Use [`.toISOString().slice(0, 10)`](https://stackoverflow.com/q/25159330/1048572) to get back a date string, or [disable the data type parsing](https://node-postgres.com/features/types). – Bergi Jul 29 '22 at 02:28

1 Answers1

-1

use moment library

example :

moment(new Date()).format("YYYY-MM-DD HH:mm:ss")
Farid Arya
  • 1
  • 2
  • 1
    Isn't moment deprecated, and no longer being maintained? ([Source](https://momentjs.com/docs/#/-project-status/).) – Jeremy Caney Jul 31 '22 at 02:16