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

NestJS SQL

This entry is part 108 of 168 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 parameter.

PostgreSQL show datestyle

The result of the above query consists of two parts:

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

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

PostgreSQL select now

In the 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 as our input.

The default is set to . 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 .

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

postSchema.prisma

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

TIME

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

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

postSchema.prisma

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

TIMESTAMP

Another column worth mentioning is the .

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

postSchema.prisma

PostgreSQL stores the timestamp as a number representing a particular moment in time. The way it is displayed can be affected by the parameter. Because the default setting is , 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 15: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  and 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 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.

The 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 type with Prisma, we need to mark our property with .

postSchema.prisma

Handling the dates in NestJS

When our users provide data, we must ensure it is valid. To do that, we can use the library. Prisma accepts the data either as the 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

The 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.

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 class.

Providing default values

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

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

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 .

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 >>
Subscribe
Notify of
guest
0 Comments
Inline Feedbacks
View all comments