How to get timestamp with timezone in typeorm?

When using TypeORM for database operations, you may need to handle time zone-related timestamps. TypeORM provides several methods to manage timestamps with time zones. Here are four approaches to handle time zone-aware timestamps:

1. Set Global Time Zone

When connecting to the database, configure the timezone option to your desired time zone. This ensures all timestamps are stored in the specified time zone. For example, when using PostgreSQL, set the time zone during connection initialization:

javascript
import { createConnection } from 'typeorm';

createConnection({
    type: "postgres",
    host: "localhost",
    port: 5432,
    username: "user",
    password: "password",
    database: "test",
    timezone: "UTC", // Set to UTC time zone
    entities: [
        // Entity list
    ],
    synchronize: true,
}).then(connection => {
    // Connection successful
}).catch(error => console.log(error));

2. Specify Time Zone in Queries

If you prefer not to set a global time zone, dynamically specify the time zone during query execution. For instance, when running SQL queries, explicitly define the time zone in the query string:

javascript
const users = await connection.manager.query(
    "SELECT *, TIMEZONE('Asia/Shanghai', creation_time) as local_time FROM users"
);

Here, the TIMEZONE('Asia/Shanghai', creation_time) function is provided by PostgreSQL to convert the creation_time column to the Asia/Shanghai time zone.

3. Handle Time Zones with Date Objects

In JavaScript, the Date object is timezone-agnostic and stores UTC timestamps. When retrieving timestamps from the database and converting them to Date objects, use libraries like moment-timezone or date-fns-tz for time zone conversions:

javascript
import moment from 'moment-timezone';

const user = await userRepository.findOne(userId);
const creationTime = moment(user.creationTime).tz('Asia/Shanghai').format();

console.log(`User creation time (Shanghai time zone): ${creationTime}`);

4. Handle Time Zones in Entity Classes

Within TypeORM entity classes, automatically manage time zone conversions using getters and setters. For example:

javascript
import { Entity, Column, PrimaryGeneratedColumn } from 'typeorm';
import moment from 'moment-timezone';

@Entity()
export class User {
    @PrimaryGeneratedColumn()
    id: number;

    @Column({ type: 'timestamp with time zone' })
    private _creationTime: Date;

    get creationTime(): string {
        return moment(this._creationTime).tz('Asia/Shanghai').format();
    }

    set creationTime(value: string) {
        this._creationTime = moment.tz(value, 'Asia/Shanghai').toDate();
    }
}

This ensures time zone conversions are automatically applied whenever you access or set the creationTime property.