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.

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 YYYY-MM-DD.

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.

The most basic type we can use is the DATE.

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

postSchema.prisma

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

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

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

postSchema.prisma

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

TIMESTAMP

Another column worth mentioning is the TIMESTAMP.

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

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

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

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

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.

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.

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.

When we do the above, Prisma generates a migration that uses the CURRENT_TIMESTAMP 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 @updatedAt.

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.