Storing an array of dates (without times)

Question

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 ] } ].

Answer 1

use moment library

example :

moment(new Date()).format("YYYY-MM-DD HH:mm:ss")