Date and time with PostgreSQL and the Drizzle ORM

Storing date and time in a database can be challenging, but it’s important to get it right. In this article, we solve this problem using PostgreSQL and the Drizzle ORM. We also delve into time zones and how to manage them in our database.

Managing dates with PostgreSQL

We can understand how our database handles dates by checking the DateStyle parameter.

The default setting for DateStyle is ISO, MDY. For information on other options, check out the official documentation.

The result of the query above has two parts:

• the default output of the date and time
• instructions on interpreting the input

By default, PostgreSQL uses the ISO 8601 standard to represent dates, which means they are displayed in the YYYY-MM-DD format.

The DateStyle parameter shows that, by default, PostgreSQL interprets dates in the month-day-year (MDY) format.

With the input format set to MDY, PostgreSQL treats the first number as the month and the second as the day. Ignoring this can cause errors when entering data.

Date columns in PostgreSQL

When defining date and time, there are several column types to choose from.

DATE

The simplest column type we can use is DATE.

1import {
2  serial,
3  text,
4  integer,
5  pgTable,
6  date,
7} from 'drizzle-orm/pg-core';
8import { sql } from 'drizzle-orm';
9 
10export const articles = pgTable('articles', {
11  id: serial('id').primaryKey(),
12  title: text('title').notNull(),
13  paragraphs: text('paragraphs').array().notNull(),
14  authorId: integer('author_id')
15    .references(() => users.id)
16    .notNull(),
17  createdAt: date('created_at')
18    .notNull()
19    .default(sql`now()`),
20});
21 
22// ...
23 
24export const databaseSchema = {
25  articles,
26  // ...
27};

The above column type lets us store the date without the time. When we fetch it from the database, it is a string by default. However, we can change it to an instance of the Date class by changing the mode parameter.

1export const articles = pgTable('articles', {
2  id: serial('id').primaryKey(),
3  title: text('title').notNull(),
4  paragraphs: text('paragraphs').array().notNull(),
5  authorId: integer('author_id')
6    .references(() => users.id)
7    .notNull(),
8  createdAt: date('created_at', {
9    mode: 'date',
10  })
11    .notNull()
12    .default(sql`now()`),
13});

When we retrieve the date from the database in the date mode, the time is set to 00:00:00.

TIME

We can use the TIME data type to store the time without the date.

1import { serial, text, integer, pgTable, time } from 'drizzle-orm/pg-core';
2 
3export const articles = pgTable('articles', {
4  id: serial('id').primaryKey(),
5  title: text('title').notNull(),
6  paragraphs: text('paragraphs').array().notNull(),
7  authorId: integer('author_id')
8    .references(() => users.id)
9    .notNull(),
10  createdAt: time('created_at').notNull(),
11});
12 
13// ...
14 
15export const databaseSchema = {
16  articles,
17};

TIMESTAMP

Another data type we should mention is the TIMESTAMP.

1import { serial, text, integer, pgTable, timestamp } from 'drizzle-orm/pg-core';
2 
3export const articles = pgTable('articles', {
4  id: serial('id').primaryKey(),
5  title: text('title').notNull(),
6  paragraphs: text('paragraphs').array().notNull(),
7  authorId: integer('author_id')
8    .references(() => users.id)
9    .notNull(),
10  createdAt: timestamp('created_at').notNull(),
11});
12 
13// ...
14 
15export const databaseSchema = {
16  articles,
17};

PostgreSQL stores timestamps as numbers representing specific moments in time. The DateStyle parameter can affect their display. With the default set to ISO, MDY, PostgreSQL shows the date in ISO format.

By default, the Drizzle ORM presents the timestamp as an instance of the Date class, but we can provide it with the mode argument.

1createdAt: timestamp('created_at', {
2  mode: 'string',
3}).notNull(),

Timezones

Coordinated Universal Time (UTC) is the primary time standard defined by atomic clocks. Timezones are typically defined by their time offset compared to UTC. For example, Eastern Standard Time (EST) is UTC -5. Therefore, if the current UTC is 15:00, the time in New York would be 10:00.

Managing time zones can be complex due to their ties to geography and politics and because of the daylight-saving adjustments. This video provides an excellent overview of the various factors to consider.

In this article, PostgreSQL didn’t perform any timezone conversions when we used the TIME and TIMESTAMP columns. This means that when we enter a specific date into the database, it remains unchanged, no matter what timezone we later use.

Instead of using the TIMESTAMP data type, we can use TIMESTAMPTZ, which allows us to include the timezone with the date. PostgreSQL then converts the input and stores it as UTC.

PostgreSQL documentation advises not to use the TIMETZ type that represents the time with a timezone. It’s not possible to take the daylight-saving time changes into account without the date information.

When we provide a timestamp in Eastern Standard Time, PostgreSQL adds 5 hours before storing it. This ensures our database remains consistent and the time stays accurate, even when data is entered from different time zones.

Dates with NestJS

Let’s create the scheduled_date column for the articles in our database using the TIMESTAMPTZ type.

1import {
2  serial,
3  text,
4  integer,
5  pgTable,
6  timestamp,
7} from 'drizzle-orm/pg-core';
8 
9export const articles = pgTable('articles', {
10  id: serial('id').primaryKey(),
11  title: text('title').notNull(),
12  paragraphs: text('paragraphs').array().notNull(),
13  authorId: integer('author_id')
14    .references(() => users.id)
15    .notNull(),
16  scheduledDate: timestamp('scheduled_date', {
17    withTimezone: true,
18  }),
19});
20 
21// ...
22 
23export const databaseSchema = {
24  articles,
25};

Let’s use the class-validator library to ensure our users provide data as a valid ISO string. Since Drizzle ORM requires us to provide dates as instances of the Date class, we need to use the class-transformer library to transform the user’s input into a string.

1import {
2  IsString,
3  IsNotEmpty,
4  IsOptional,
5  IsNumber,
6  IsDate,
7} from 'class-validator';
8import { Type } from 'class-transformer';
9 
10export class CreateArticleDto {
11  @IsString({ each: true })
12  @IsNotEmpty({ each: true })
13  paragraphs: string[];
14 
15  @IsString()
16  @IsNotEmpty()
17  title: string;
18 
19  @IsOptional()
20  @IsNumber({}, { each: true })
21  categoryIds: number[] = [];
22 
23  @IsDate()
24  @Type(() => Date)
25  @IsOptional()
26  scheduledDate?: Date;
27}

We can now use Drizzle ORM to create an article with a scheduled date, for example.

1import { Injectable } from '@nestjs/common';
2import { DrizzleService } from '../database/drizzle.service';
3import { databaseSchema } from '../database/database-schema';
4import { CreateArticleDto } from './dto/create-article.dto';
5 
6@Injectable()
7export class ArticlesService {
8  constructor(private readonly drizzleService: DrizzleService) {}
9 
10  async create(article: CreateArticleDto, authorId: number) {
11    const createdArticles = await this.drizzleService.db
12      .insert(databaseSchema.articles)
13      .values({
14        authorId,
15        title: article.title,
16        paragraphs: article.paragraphs,
17        scheduledDate: article.scheduledDate,
18      })
19      .returning();
20 
21    return createdArticles.pop();
22  }
23 
24  // ...
25}

Summary

In this article, we’ve covered different methods for storing the date and time in PostgreSQL databases. We also used the Drizzle ORM to define various date column types in our schema and used the class-validator library to validate dates provided through the API.

Dealing with timezones can be quite a headache. Thanks to using the timestamp with timezone type in PostgreSQL, we can keep our data consistent, no matter which timezones our users are in, minimizing the chances of timezone-related problems.