Easy Guide to TypeORM Dates, Time, TimeStamp, and Timezone

TypeORM gives you a headstart when working with dates, times, time zones, and timestamps. In Nest.js, using TypeORM, you’ll need to manage your entities and database columns to handle dates, times, and timestamps.

TypeORM will give you column decorators precisely for creating these elements. Dive into this guide and learn to use TypeORM column decorators to manage date, time, Timezone, and timestamps within your Nest.js entities.

The Common TypeORM Date and Time Column Decorators

When working with TypeORM, Decorators dictate the characteristics of your table columns. You want to column to inherit dates or times within your entities. The familiar decorators here are:

• date
• time
• timestamp
• @DeleteDateColumn
• @UpdateDateColumn
• @CreateDateColumn

These Decorators will then use Column types to describe the complete characteristics of individual attributes. Let’s look at some examples.

Let’s assume you want to create two different columns, with Date and DateTime. TypeORM will give you some custom Decorators, such as:

• Created Date, with @CreateDateColumn() decorator.
• When the item was last Updated with the @UpdateDateColumn() decorator.
• The time an item was deleted. This will allow TypeORM to record only when soft delete is enabled with the @DeleteDateColumn() decorator.

With this example, your entries will have the following special date fields;

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

This should be the same as:

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

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

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

Using Custom Date, time and timestamp

As you go further, you may need to add dates based on your preference. This will apply if you want to use something other than the above decorators.

The most easy way to create TypeORM date and time is to use column of respective types as follows:

// Use Time type for time 
@Column({ type: 'time' })
timeOnly: string;
// Use date type for date 
@Column({ type: 'date' })
dateOnly: string;

TypeORM specifies custom date formats for your columns with @CreateDateColumn and @UpdateDateColumn decorators to manage creating and updating timestamps as follows automatically:

import { Entity, PrimaryGeneratedColumn, CreateDateColumn, UpdateDateColumn } from 'typeorm';

@Entity()
export class SampleEntity {
  @PrimaryGeneratedColumn()
  id: number;

  @CreateDateColumn({ type: 'timestamp', default: () => 'CURRENT_TIMESTAMP' })
  createdAt: Date;

  @UpdateDateColumn({ type: 'timestamp', default: () => 'CURRENT_TIMESTAMP', onUpdate: 'CURRENT_TIMESTAMP' })
  updatedAt: Date;
}

This will have the following custom fields using date field, time field, and timestamp field:

import { Entity, Column, PrimaryGeneratedColumn } from 'typeorm';

@Entity()
export class SampleEntity {
  @PrimaryGeneratedColumn()
  id: number;

  @Column({ type: 'date', default: () => 'CURRENT_DATE' })
  dateField: Date;

  @Column({ type: 'time', default: () => 'CURRENT_TIME' })
  timeField: Date;

  @Column({ type: 'timestamp', default: () => 'CURRENT_TIMESTAMP' })
  timestampField: Date;
}

Guide To TypeORM MSSQL With Nest.js and Typescript

Customizing TypeORM Date and Timestamp Behavior

A simple TypeORM timestamp should create a complete date object using timestamp type as follows:

@Column({ type: 'timestamp', nullable: true })
timestamp: Date;

The following example sets default values for your date, time, and timestamp fields using the default option:

@Column({ type: 'date', default: () => 'CURRENT_DATE' })
dateField: Date;

@Column({ type: 'time', default: () => 'CURRENT_TIME' })
timeField: Date;

@Column({ type: 'timestamp', default: () => 'CURRENT_TIMESTAMP' })
timestampField: Date;

Now, you can still dive deeper and use transformers to control how values are transformed before saving them to the database and after retrieving them. For example:

@Column({ 
    type: 'timestamp', 
    transformer: {
        to: (value: Date) => value,
        from: (value: string) => new Date(value)
    },
})
timestampField: Date;

Adding Timezone Support with TypeORM

TypeORM uses the Timezone of the database by default. You will have two Time Zone Strategies:

• Store all timestamps in the database in Coordinated Universal Time (UTC). This will Convert them to the user’s local time zone when displaying them.
• Store timestamps with time zone information.

Timezone works almost the same as TimeStamp. TimeStamp provide a complete date string. Therefore, you only need to add timezone to a timestamp and create a complete TypeORM timezone date.

Based on this, TypeORM uses timestamp. You now need to add timestamp with timezone column as follows:

@Column({ type: 'timestamptz', nullable: true })
timestampWithTimezone: Date;

TypeORM Date, Time, and Timestamp with Specific Database

Handling dates, times, and timestamps will vary based on the database you are using. Let’s check some examples of working with TypeORM.

• Datetime vs Timestamp with MySQL/MariaDB

Datetime stores points in time, while timestamp is for automatic updates such as updatedAt fields. You will represent them as follows:

@Column({ type: 'datetime', default: () => 'CURRENT_TIMESTAMP' })
datetimeField: Date;

@Column({ type: 'timestamp', default: () => 'CURRENT_TIMESTAMP', onUpdate: 'CURRENT_TIMESTAMP' })
timestampField: Date;

• SQLite is less strict about data types. You can store dates and times as text. TypeORM handles the mapping for you:

@Column({ type: 'datetime', default: () => 'CURRENT_TIMESTAMP' })
datetimeField: Date;

@Column({ type: 'text', default: () => 'CURRENT_TIMESTAMP', onUpdate: 'CURRENT_TIMESTAMP' })
timestampField: Date;

• MongoDB uses TypeORM with specific decorators like @Field(type => Date) for handling dates:

@Field(type => Date)
createdAt: Date;

@Field(type => Date)
updatedAt: Date;

Conclusion

I hope you have learned how to use ypeORM Dates, Time, TimeStamp, and Timezone in your entities.