Question 89273 in PostgreSQL Question posted in
The official documentation can be found here.

Format date dd/mm/yyyy keeping Date type TypeORM – Postgresql

I need to insert the dates in dd/mm/yyyy format to match the other technologies in my application. I am using Nestjs, TypeOrm and Postgresql as database.

I tried to use the @Transform decorator like in the documentation

  @Transform(({ value }) => moment(value).format('DD/MM/YYYY'))
  @Column()
  encoded_date: Date;

Except that it always inserts a timestamp and returns a timestamp.
Is it possible to have a similar behavior to_date(’25/02/2023′, ‘DD/MM/YYYY’)::date, as one does in an sql script ? And Keeping Date type ?

I also tried this and that’s give me yyyy-mm-dd but no way to format
like dd/mm/yyyy

 @CreateDateColumn({type:'date'})
  encoded_date: Date;
Tags:

2

Answers


  1. 0

    On db-end, you can alter your datestyle setting to modify default input text-to-date and output date-to-text formats (see also here). As with most settings:

    • this can be set differently in transaction, session, role, database, system contexts, in that order of priority
    • each session can override the setting, which some ORMs (can be configured to) do on connection init and each client can additionally issue a SET of its own
    • the change won’t affect other, ongoing sessions

    You can also interface with your data through formatting functions in both directions, using:

    • to_date(your_date,'DD/MM/YYYY') on input, which you mentioned
    • to_char(your_date,'DD/MM/YYYY') on output.

    demo

    show datestyle;
-- DateStyle
-------------
-- ISO, MDY
select 'today'::date;
--    date
--------------
-- 2023-03-24
select '03/24/2023'::date;
--ERROR:  date/time field value out of range: "03/24/2023"
--LINE 2: select '03/24/2023'::date;
--               ^
--HINT:  Perhaps you need a different "datestyle" setting.
select to_char(to_date('03/24/2023','MM/DD/YYYY'), 'MM/DD/YYYY');
--  to_char
--------------
-- 03/24/2023
set datestyle='SQL, MDY';
SET
select 'today'::date;
--    date
--------------
-- 03/24/2023
select '03/24/2023'::date;
--    date
--------------
-- 03/24/2023

    As to the underlying format of the date/time/timestamp, with or without timezone: it’s always just a number. Postgres uses the format mask given or implied by settings to calculate an epoch, then the timezone implied by settings or a supplied time zone name or offset, to shift it to internal UTC – and after that it doesn’t keep them. These types are meant to hold a when. Where and how help pinpoint the exact, absolute when, but they aren’t its integral elements, so the source timezone and format it came in are discarded unless saved separately.

    Login or Signup to reply.
  2. 0

    You can use custom transfomer than converts input string to Date object.

    @Column({
    type: 'date',
    transformer: {
      from: (value: string) => new Date(value),
      to: (value: Date) => value.toISOString().slice(0, 10), // format the Date to YYYY-MM-DD
    },
  })
  encoded_date: Date;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search