How can I create columns with type Date and type DateTime in nestjs with typeORM?

Question

I am new with nestjs. How can I set columns that accepts Date format and dateTime format?

Not in both cases, the columns are two differents column, one accept Date and other dateTime.

Cid

Answer 1

You can see the docs here that explains the @Column decorator. In @Column there is an option called type -> here is where you specify which type of date you want to store for that specific column.

More on column types here.

For example (using PostgreSQL):

@Column({ type: 'date' })
date_only: string;

@Column({ type: 'timestamptz' }) // Recommended
date_time_with_timezone: Date;

@Column({ type: 'timestamp' }) // Not recommended
date_time_without_timezone: Date;

Note that date_only is of type string. See this issue for more information.

Moreover, automatic dates for certain events are possible: 

• Creation Date, available via @CreateDateColumn() decorator.
• Last Updated Date, available via @UpdateDateColumn() decorator.
• Deletion Date, only when soft delete is enabled, available via @DeleteDateColumn() decorator.

@CreateDateColumn()
created_at: Date; // Creation date

@UpdateDateColumn()
updated_at: Date; // Last updated date

@DeleteDateColumn()
deleted_at: Date; // Deletion date

Waken

This answer is not correct. The type returned by typeorm for type: 'date' is a string - not a date object with the time set to 0. See github.com/typeorm/typeorm/issues/2176 – Dacosta

@Dacosta I've updated my answer. Let me know what you think! – Waken

@CarloCorradini awesome! I like that you clearly recommend timestamptz too ;) – Dacosta

would timestamp be ok when using mysql? as timestamptz is not supported? – Unwitnessed

Ok, but how to use "timestamptz" with GraphQL? What should I use inside ObjectType and InputType to fix the issue with the wrong DateTime types? – Kinsler

@EugeneZalivadnyi I use GraphQL Scalars library with a date/time scalar mapping. E.g. GraphQLTimestamp for UNIX epoch or GraphQLDateTime for UTC. You decide what best suits your needs since the input given to the scalar is always a Date instance (timestamptz). – Waken

@CarloCorradini any reason why "timestamptz" is recommended over "timestamp"? – Krauss

@Krauss See https://mcmap.net/q/356065/-postgresql-jdbc-and-timestamp-vs-timestamptz – Waken

Anyone who comes here and doesn't find how to include the columns #64941648 – Kokoschka

Answer 2

How about ?

@CreateDateColumn()
created_at: Date;
    
@UpdateDateColumn()
updated_at: Date;

EDIT

You can find more info here

Leukas

This should be the correct answer, simple and handled automatically by typeorm on save. – Gulledge

I don't believe this should be the correct answer, or upvoted as it is. There are more uses for timestamp columns than just created_at and updated_at values – Andra

@Andra that's why I provided a link to the rest of the docs :D – Leukas

Answer 3

To add more to this ticket as DateTime was not even mentioned:

  /**
   * Start DateTime
   */
  @Column({
    type: 'datetime',
    default: () => 'NOW()',
  })
  @Index()
  start: string;

  /**
   * End DateTime
   */
  @Column({
    type: 'datetime',
    nullable: true,
  })
  @Index()
  end: string;

This is for those not wanting or needing to use

@CreateDateColumn()

@UpdateDateColumn()

Unwitnessed

Does this mean that start and end are of type string and not Date? – Primeval

No, they are type: 'datetime' – Unwitnessed

Yes, that is w.r.t MySQL. I wanted to know about how they would appear in JS. – Primeval

Answer 4

This is how I do it. The name parameter represents the column name in the database. If you use the built in decorators it will handle updating the columns in without additional code.

@CreateDateColumn({ name: 'created_at'})
createdAt: Date;

@UpdateDateColumn({ name: 'updated_at' })
updatedAt: Date;

@DeleteDateColumn({ name: 'deleted_at' })
deletedAt: Date;

Libove

Answer 5

Just to complement... When creating and wanting the result to only display the date, use:

@CreateDateColumn({ type: "date" }) createdAt: Date | string;

If not specified, the time will also be included.

Petrolatum

As it’s currently written, your answer is unclear. Please edit to add additional details that will help others understand how this addresses the question asked. You can find more information on how to write good answers in the help center. – Deliberative

Answer 6

I could not get this to work in any way, seems to be a bug in the decorators or a misunderstanding how they should work (NestJS 10.3, TypeORM 0.3.17, Postgres 15). I used this workaround with hooks instead:

class SomeEntity {

    @Column({
        name: 'created_at',
        type: 'timestamptz',
    })
    createdAt?: Date;

    @Column({
        name: 'updated_at',
        type: 'timestamptz',
    })
    updatedAt?: Date;

    @BeforeInsert()
    protected setCreatedAt(): void {
        this.createdAt = new Date();
    }

    @BeforeUpdate()
    protected setUpdatedAt(): void {
        this.updatedAt = new Date();
    }
}

Shavian