API with NestJS #132. Handling date and time in PostgreSQL with Kysely

November 6, 2023

This entry is part 132 of 177 in the API with NestJS

Saving date and time in our database can be tricky, but it’s crucial to do it correctly. In this article, we address this problem using PostgreSQL and Kysely. We also explore time zones and how to handle them when designing our database.

Dates in PostgreSQL

We can determine how our database handles dates by examining the DateStyle parameter.

By default, DateStyle is set to ISO, MDY. To learn about other options, take a look at the official documentation.

The result of the query above includes two parts:

the default date and time output,
instructions on how to understand the input.

By default, PostgreSQL represents dates following the ISO 8601 standard, which means the default display format is YYYY-MM-DD.

The DateStyle parameter also tells us that PostgreSQL interprets given dates as month-day-year (MDY).

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

Date columns built into PostgreSQL

There are various column types to pick from when describing the date and time.

DATE

The most straightforward column type we can choose is DATE.

20231105201749_add_created_at_to_articles.ts

import { Kysely } from 'kysely';

export async function up(database: Kysely<unknown>): Promise<void> {

await database.schema

.alterTable('articles')

.addColumn('created_at', 'date')

.execute();

}

export async function down(database: Kysely<unknown>): Promise<void> {

await database.schema.alterTable('articles').dropColumn('created_at');

}

This column allows us to store the date without the time. When we retrieve the data from the database, it is an instance of the Date class.

articlesTable.ts

import { Generated } from 'kysely';

export interface ArticlesTable {

id: Generated<number>;

title: string;

paragraphs: string[];

author_id: number;

created_at?: Date;

}

In the dates retrieved from the database, the time is set to 00:00:00.

articles.repository.ts

import { Database } from '../database/database';

import { Article } from './article.model';

import { Injectable } from '@nestjs/common';

@Injectable()

export class ArticlesRepository {

constructor(private readonly database: Database) {}

async getById(id: number) {

const databaseResponse = await this.database

.selectFrom('articles')

.where('id', '=', id)

.selectAll()

.executeTakeFirst();

if (databaseResponse) {

console.log(databaseResponse.created_at instanceof Date); // true

// Sun Nov 05 2023 00:00:00 GMT+0100 (Central European Standard Time)

console.log(databaseResponse.created_at?.toString());

return new Article(databaseResponse);

}

// ...

}

TIME

If we want to store the time without the date, we can use the TIME column.

20231105201749_add_created_at_to_articles.ts

import { Kysely } from 'kysely';

export async function up(database: Kysely<unknown>): Promise<void> {

await database.schema

.alterTable('articles')

.addColumn('created_at', 'time')

.execute();

}

export async function down(database: Kysely<unknown>): Promise<void> {

await database.schema.alterTable('articles').dropColumn('created_at');

}

When using the TIME column, the retrieved data is a string.

articlesTable.ts

import { Generated } from 'kysely';

export interface ArticlesTable {

id: Generated<number>;

title: string;

paragraphs: string[];

author_id: number;

created_at?: string;

}

We can investigate that in our repository.

articles.repository.ts

import { Database } from '../database/database';

import { Article } from './article.model';

import { Injectable } from '@nestjs/common';

@Injectable()

export class ArticlesRepository {

constructor(private readonly database: Database) {}

async getById(id: number) {

const databaseResponse = await this.database

.selectFrom('articles')

.where('id', '=', id)

.selectAll()

.executeTakeFirst();

if (databaseResponse) {

console.log(typeof databaseResponse.created_at); // string

console.log(databaseResponse.created_at); // 13:15

return new Article(databaseResponse);

}

// ...

}

TIMESTAMP

Another important date column is the TIMESTAMP.

20231105201749_add_created_at_to_articles.ts

import { Kysely } from 'kysely';

export async function up(database: Kysely<unknown>): Promise<void> {

await database.schema

.alterTable('articles')

.addColumn('created_at', 'timestamp')

.execute();

}

export async function down(database: Kysely<unknown>): Promise<void> {

await database.schema.alterTable('articles').dropColumn('created_at');

}

PostgreSQL stores the timestamp as a numeric value representing a specific moment in time. The way it’s displayed can be influenced by the DateStyle parameter. Since the default is set to ISO, MDY, PostgreSQL displays the date in the ISO format.

When using the TIMESTAMP column, the retrieved data is an instance of the Date class.

articlesTable.ts

import { Generated } from 'kysely';

export interface ArticlesTable {

id: Generated<number>;

title: string;

paragraphs: string[];

author_id: number;

created_at?: Date;

}

The data that comes from the database includes both the date and the time.

articles.repository.ts

import { Database } from '../database/database';

import { Article } from './article.model';

import { Injectable } from '@nestjs/common';

@Injectable()

export class ArticlesRepository {

constructor(private readonly database: Database) {}

async getById(id: number) {

const databaseResponse = await this.database

.selectFrom('articles')

.where('id', '=', id)

.selectAll()

.executeTakeFirst();

if (databaseResponse) {

console.log(databaseResponse.created_at instanceof Date); // true

// Sun Nov 05 2023 15:30:34 GMT+0100 (Central European Standard Time)

console.log(databaseResponse.created_at?.toString());

return new Article(databaseResponse);

}

// ...

}

Timezones

The Coordinated Universal Time (UTC) is the primary time standard determined by atomic clocks. Timezones are usually defined by the number of hours ahead or behind UTC.

For instance, Eastern Standard Time (EST) can be expressed as UTC -5. So, if the current UTC is 20:00, the time in New York would be 15:00.

Dealing with time zones can be challenging, as they are linked to geography and politics and can be influenced by daylight saving adjustments. This video offers a great overview of various factors to keep in mind.

PostgreSQL didn’t perform any timezone-related conversions when we used the TIME and TIMESTAMP columns in this article. This means that when we input a specific date into our database, it will remain the same, regardless of the timezone we later use to display it.

Both of these types have counterparts that take time zones into account. When we use the TIMESTAMPTZ data type, we can provide the timezone and date. PostgreSQL then converts our input and stores it as UTC.

The official PostgreSQL documentation advises against using the TIMETZ type, which represents time with a timezone. Without date information, it would not be possible to account for daylight-saving time changes.

When we provide a timestamp labeled as Eastern Standard Time, PostgreSQL adds 5 hours before saving it. This ensures that our database remains consistent, and our time stays accurate, even if we input data using different timezones.

Let’s add the scheduled_date column to our articles and use the TIMESTAMPTZ column type.

20231105221040_add_scheduled_date_to_articles.ts

import { Kysely } from 'kysely';

export async function up(database: Kysely<unknown>): Promise<void> {

await database.schema

.alterTable('articles')

.addColumn('scheduled_date', 'timestamptz')

.execute();

}

export async function down(database: Kysely<unknown>): Promise<void> {

await database.schema.alterTable('articles').dropColumn('scheduled_date');

}

Inserting the date into the database

To ensure the validity of the data our users provide, we can use the class-validator library. Kysely accepts data in either the Date class or as an ISO string. Let’s require the users to provide the ISO strings.

article.dto.ts

import {

IsString,

IsNotEmpty,

IsOptional,

IsNumber,

IsISO8601,

} from 'class-validator';

export class ArticleDto {

@IsString()

@IsNotEmpty()

title: string;

@IsString({ each: true })

@IsNotEmpty({ each: true })

paragraphs: string[];

@IsOptional()

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

categoryIds?: number[];

@IsISO8601({

strict: true,

})

@IsOptional()

scheduledDate?: string;

}

The strict parameter ensures that the given date is valid, taking into account factors such as leap days.

Since we allow the users to provide the date as an ISO string, but retrieve it from the database using the Date class, we should use the ColumnType generic type.

articlesTable.ts

import { ColumnType, Generated } from 'kysely';

export interface ArticlesTable {

id: Generated<number>;

title: string;

paragraphs: string[];

author_id: number;

created_at?: Date;

scheduled_date?: ColumnType<Date, string | Date, string | Date>;

}

The next step is to adjust our model.

article.model.ts

export interface ArticleModelData {

id: number;

title: string;

paragraphs: string[];

author_id: number;

scheduled_date?: Date;

}

export class Article {

id: number;

title: string;

paragraphs: string[];

authorId: number;

scheduledDate?: Date;

constructor({

id,

title,

paragraphs,

author_id,

scheduled_date,

}: ArticleModelData) {

this.id = id;

this.title = title;

this.paragraphs = paragraphs;

this.authorId = author_id;

if (scheduled_date) {

this.scheduledDate = scheduled_date;

}

We can now use our new property when creating the articles.

articles.repository.ts

import { Database } from '../database/database';

import { Article } from './article.model';

import { BadRequestException, Injectable } from '@nestjs/common';

import { ArticleDto } from './dto/article.dto';

import { PostgresErrorCode } from '../database/postgresErrorCode.enum';

import { isDatabaseError } from '../types/databaseError';

@Injectable()

export class ArticlesRepository {

constructor(private readonly database: Database) {}

async create(data: ArticleDto, authorId: number) {

try {

const databaseResponse = await this.database

.insertInto('articles')

.values({

title: data.title,

paragraphs: data.paragraphs,

author_id: authorId,

scheduled_date: data.scheduledDate,

})

.returningAll()

.executeTakeFirstOrThrow();

return new Article(databaseResponse);

} catch (error) {

if (!isDatabaseError(error)) {

throw error;

}

if (error.code === PostgresErrorCode.CheckViolation) {

throw new BadRequestException(

'The length of the content needs to be greater than 0',

);

}

if (error.code === PostgresErrorCode.NotNullViolation) {

throw new BadRequestException(

`A null value can't be set for the ${error.column} column`,

);

}

throw error;

}

// ...

}

Default values

We do not always have to provide the date manually. Instead, let’s adjust our migration that adds the created_at column.

import { Kysely, sql } from 'kysely';

export async function up(database: Kysely<unknown>): Promise<void> {

await database.schema

.alterTable('articles')

.addColumn('created_at', 'timestamptz', (column) => {

return column.notNull().defaultTo(sql`now()`);

})

.execute();

}

export async function down(database: Kysely<unknown>): Promise<void> {

await database.schema.alterTable('articles').dropColumn('created_at');

}

Above, we use the now() function built into PostgreSQL to provide the default value for the created_at column. Since we provide a default value, we can also make this column non-nullable and don’t worry about the articles we already have in the database.

Summary

In this article, we’ve explored different methods for storing date and time data with PostgreSQL. We’ve also learned how to use Kysely to define various types of date columns in our schema. Besides that, we’ve used the class-validator library to validate the dates provided through the API.

Timezones can be the source of various bugs and problems. By utilizing the timestamp with timezone type in PostgreSQL, we can maintain data consistency, regardless of the time zones in which our users provide the data. Thanks to that, we can minimize the chance of timezone-related issues.

Series Navigation<< API with NestJS #131. Unit tests with PostgreSQL and KyselyAPI with NestJS #133. Introducing database normalization with PostgreSQL and Prisma >>