API with NestJS #108. Date and time with Prisma and PostgreSQL

May 15, 2023

This entry is part 108 of 121 in the API with NestJS

Storing date and time in our database might be tricky, but it is essential to get it right. In this article, we tackle this issue using PostgreSQL and Prisma. We also learn the concept of timezones and how to deal with them when designing our database.

How PostgreSQL interprets dates

We can learn how our database represents dates by reading the DateStyle parameter.

PostgreSQL show datestyle

The result of the above query consists of two parts:

the default date/time output
how to interpret the input.

By default, PostgreSQL represents the dates using the ISO 8601 standard. Because of that, the default display format is YYYY-MM-DD.

PostgreSQL select now

In the DateStyle parameter, we can see that by default, PostgreSQL interprets provided dates as month-day-year (MDY).

Since the input format is set to MDY, the first number is treated as a month and the second as a day. Not taking it into account could result in an error when inputting our data.

When the input format is set to MDY, we could also use YYYY-MM-DD as our input.

The default DateStyle is set to ISO, MDY. However, therte are other possibilities. For more, check out the official documentation.

Basic columns built into PostgreSQL to manage dates

There are quite a few different column types we can choose to describe the date and the time.

DATE

The most basic type we can use is the DATE.

1	ALTER TABLE "Post" ADD COLUMN "createdAt" DATE NOT NULL;

To add this column, we need to modify our schema by adding a parameter with the DateTime marked with @db.Date.

postSchema.prisma

model Post {

id Int @id @default(autoincrement())

title String

content String

author User @relation(fields: [authorId], references: [id])

authorId Int

categories Category[]

createdAt DateTime @db.Date

@@index([authorId])

}

This straightforward type allows us to store a particular date without the time.

TIME

To store the time alone, we can use the TIME data type.

1	ALTER TABLE "Post" ADD COLUMN "createdAt" TIME NOT NULL;

To add this column with Prisma, we need to add a parameter marked with @db.Time.

postSchema.prisma

model Post {

id Int @id @default(autoincrement())

title String

content String

author User @relation(fields: [authorId], references: [id])

authorId Int

categories Category[]

createdAt DateTime @db.Time

@@index([authorId])

}

It represents time with hours, minutes, and seconds that include a fractional value.

TIMESTAMP

Another column worth mentioning is the TIMESTAMP.

1	ALTER TABLE "Post" ADD COLUMN "createdAt" TIMESTAMP NOT NULL;

To configure it in our schema, we need to mark our property with @db.Timestamp.

postSchema.prisma

model Post {

id Int @id @default(autoincrement())

title String

content String

author User @relation(fields: [authorId], references: [id])

authorId Int

categories Category[]

createdAt DateTime @db.Timestamp

@@index([authorId])

}

PostgreSQL stores the timestamp as a number representing a particular moment in time. The way it is displayed can be affected by the DateStyle parameter. Because the default setting is ISO, MDY, PostgreSQL shows the date in the ISO format.

Timezones

The coordinated universal time (UTC) is the primary time standard used worldwide and is defined by atomic clocks. Timezones are most often defined by the difference in hours from the UTC. A good example is Eastern Standard Time (EST) which can be described as UTC -5. If the current UTC is 20:00, the time in New York would be 10:00.

Having to deal with timezones can be a reason for quite a headache. Timezones depend on geography and politics and can be affected by daylight saving changes. This video gives an excellent summary of different aspects to consider.

When we used the TIME and TIMESTAMP types so far in this article, PostgreSQL did no timezone-related conversions. Therefore, when we enter a particular date into our database, it will always stay the same regardless of what timezone we display it in later.

The above two types have their versions that consider the timezone. When we use the TIMESTAMPTZ data type, we specify the timezone when providing the data. PostgreSQL then converts our input and stores it as UTC.

Since we provide a timestamp marked as Eastern Standard Time, PostgreSQL would add 5 hours before storing it. Thanks to that, even if we input data using various timezones, our database is consistent, and our time is not mismatched.

1	ALTER TABLE "Post" ADD COLUMN "scheduledDate" TIMESTAMPTZ;

The TIMETZ type that is the time with timezone is discouraged by the official PostgreSQL documentation. Without the information about the date it would be impossible to account for the daylight-saving time.

To use the TIMESTAMPTZ type with Prisma, we need to mark our property with @db.Timestamptz.

postSchema.prisma

model Post {

id Int @id @default(autoincrement())

title String

content String

author User @relation(fields: [authorId], references: [id])

authorId Int

categories Category[]

scheduledDate DateTime @db.Timestamptz

@@index([authorId])

}

Handling the dates in NestJS

When our users provide data, we must ensure it is valid. To do that, we can use the class-validator library. Prisma accepts the data either as the Date class built into JavaScript or as an ISO string. Since that is the case, let’s force the users to provide ISO strings.

createPost.dto.ts

import {

IsString,

IsNotEmpty,

IsNumber,

IsOptional,

IsISO8601,

} from 'class-validator';

export class CreatePostDto {

@IsString()

@IsNotEmpty()

title: string;

@IsString()

@IsNotEmpty()

content: string;

@IsOptional()

@IsNumber({}, { each: true })

categoryIds?: number[];

@IsISO8601({

strict: true,

})

@IsOptional()

scheduledDate?: string;

}

The strict parameter makes sure that the provided date is valid in terms of leap days, for example.

We now need to use the new property when creating our entity.

async createPost(post: CreatePostDto, user: User) {

const categories = post.categoryIds?.map((category) => ({

id: category,

}));

return this.prismaService.post.create({

data: {

title: post.title,

content: post.content,

scheduledDate: post.scheduledDate,

author: {

connect: {

id: user.id,

categories: {

connect: categories,

include: {

categories: true,

});

}

Thanks to all of the above, we can now make a POST request that includes a date.

It’s also important to know that when we query our data from the database, Prisma converts it to instances of the Date class.

async getPostById(id: number) {

const post = await this.prismaService.post.findUnique({

where: {

id,

});

if (!post) {

throw new PostNotFoundException(id);

}

console.log(post.scheduledDate instanceof Date); // true

return post;

}

Providing default values

Prisma can handle some dates for us automatically. By adding @default(now()) to our date, we ensure that Prisma provides the current date and time when the entity is created.

1	createdAt DateTime @default(now()) @db.Timestamptz

When we do the above, Prisma generates a migration that uses the CURRENT_TIMESTAMP value provided by PostgreSQL.

1	ALTER TABLE "Post" ADD COLUMN "createdAt" TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP;

Besides the creation time, PostgreSQL can also handle the time when the record was last updated. To do this, we need to mark our property with @updatedAt.

1	updatedAt DateTime @updatedAt @db.Timestamptz

When we do the above, Prisma automatically sets the value for this field whenever we update the entity.

Summary

In this article, we’ve gone through various ways of storing the date and time with PostgreSQL. We’ve also learned how to use Prisma to define multiple types of date-related columns in our schema. On top of that, we managed to handle dates in our NestJS application.

The timezones can be the cause of various bugs and issues. Thanks to using the timestamp with timezone type available in PostgreSQL, we can ensure that our data is consistent regardless of what timezones our users are providing the data in.

Series Navigation<< API with NestJS #107. Offset and keyset pagination with PrismaAPI with NestJS #109. Arrays with PostgreSQL and Prisma >>