Update/Create DateColumn as Unix Timestamp

[DJWassink]

I am currently working with a database which has a created_at and updated_at column formatted as a unix timestamp.

It would be pretty easy to simply write those on every write/update I do but this would pretty much defeat the purpose of a orm right? So basically wouldn't it be nice if there would also be a option to let those decorators update the columns as a timestamp instead of a Date object?

I guess it could either work by overwriting the type to a number of create another decorator called something like UpdateTimestampColumn & CreateTimestampColumn?

[pleerock]

Yes I think this can be additional feature to those decorators.

[MichalLytek]

And here we have a name convention battle again 😆

I don't like an idea of introducing a new decorator CreateTimestampColumn which does the same as CreateDateColumn but in different format. I would like to see and decorator option format: "timestamp"|"date" so it would look like this.

@CreateDateColumn()
createdAt: string;

@UpdateDateColumn({ type: "timestamp" })
updatedAt: number;

And of course it would be set by default to "date" and it should be configurable globally if you use everywhere unix timestamp.

[pleerock]

nope we won't introduce a new decorator for this. Just type for @UpdateDateColumn as you wrote

[svicalifornia]

Any update on this?

[AlexMesser]

Please check this issue in latest @next version. These columns now supports different date types.
E. g.

    @CreateDateColumn({type: "timestamp"})
    createdAt: Date;

    @UpdateDateColumn({type: "timestamp"})
    updatedAt: Date;

[yuluyi]

The deserialization still has some problems. timestamp still deserializes to Date even if I go createdAt: number

[EricRabil]

If this is a UNIX timestamp shouldn't there be the option to use a number type to simplify things?

[EricRabil]

I believe OP was asking if they could get the raw timestamp instead of a Date object - I don't feel that this issue has been resolved. Can OP clarify?

[matthewlilley]

Yeah, this is still an issue somewhat. To get the correct unix time from a timestamp, the column has to be selected like this in MySQL.

SELECT UNIX_TIMESTAMP(createdAt) FROM round ORDER BY id DESC LIMIT 1

Which will give you the correct result, no matter what the timezone setting is on your MySQL server.

Is it possible to setup createdAt and updatedAt to be selected this way, so it'll return a unix timestamp?

[dhessler]

I also have the same problem, in my case I'd like to get an ISO8601 string instead of Date for a timestamptz column, e.g.:

@CreateDateColumn({ type: "timestamptz" })
public createdAt?: string;

UPDATE:
I solved this with a transformer:

@CreateDateColumn({
  type: "timestamptz",
  transformer: {
    from: (value?: Date | null) =>
        value === undefined || value === null ? value : value.toISOString(),
    to: (value?: string | null) =>
        value === undefined || value === null ? value : new Date(value)
  }
})
public createdAt?: IsoDate;

This is a bit cumbersome though, also I'm not sure of the performance overhead in using a transformer vs something that could be provided natively by typeorm.

[mikepascual]

Any updates on this? I would like my date_updated and date_created columns in MySQL to be defined as type BIGINT and I'd like UpdateTimestampColumn() & CreateTimestampColumn() to set them as UNIX timestamps as integers (e.g. 1557976474). I think OP might have had the same request.

[matthewlilley]

You probably want to use native MySQL dates, and mutate to a unix timestamp if/when you need that.

[Darkein]

I have the same problem and unfortunatily I can't update the database (it's a shared database between multiple services). I don't know how I can do it without using the classic @column, @beforeInsert and @BeforeUpdate decorators for now

[svicalifornia]

@matthewlilley MySQL and other databases stores date/time values as numbers internally. The OP and others on this thread are asking to read/write date/time values as numbers via TypeORM.

Are you seriously suggesting that it's better to ask MySQL to serialize these values as date strings and then parse every single date value retrieved in JavaScript to get it back into a number?

[matthewlilley]

@svicalifornia I understand what you're saying, but typeorm doesn't support this functionality, at least it didn't the last time I checked. You can SELECT UNIX_TIMESTAMP(createdAt), or mutate the date in your own way depending on your needs. I don't save dates as unix in MySQL, but sometimes I want to access a date as unix.

[Darkein]

that's what I did, if it can help

import { BeforeInsert, BeforeUpdate, Column } from 'typeorm';
import { Type } from 'class-transformer';

export class BaseEntity {
  @Column({
    type: 'int',
    width: 11,
    nullable: false,
    readonly: true,
    default: () => '0',
    transformer: {
      to: (value?: Date) => (!value ? value : Math.round(value.getTime() / 1000)),
      from: (value?: number) => (!value ? value : new Date(value * 1000))
    }
  })
  @Type(() => Date)
  createdAt: Date;

  @Column({
    type: 'int',
    width: 11,
    nullable: true,
    default: () => null,
    transformer: {
      to: (value?: Date) => (!value ? value : Math.round(value.getTime() / 1000)),
      from: (value?: number) => (!value ? value : new Date(value * 1000))
    }
  })
  @Type(() => Date)
  updatedAt?: Date;

  @BeforeInsert()
  updateDateCreation() {
    this.createdAt = new Date();
  }

  @BeforeUpdate()
  updateDateUpdate() {
    this.updatedAt = new Date();
  }
}

[sgronblo]

I tried to use the construct by @dhessler as in specifying the type explicitly and using a transformer, but typeorm is complaining that postgres doesn't support luxon.DateTime.

[hengkx]

Any update on this?

[leandrofreire08]

I don't know if it's helpful, but this works for me:

@Column({ type: "timestamp", default: () => "CURRENT_TIMESTAMP"})
  createdAt: string;

  @Column({ type: "timestamp", default: () => "CURRENT_TIMESTAMP"})
  updatedAt: string;

[jotaoncode]

@leandrofreire08 solutions works for me too, at least for a mysql db I would say... I saw other posts that proposes () => "now()"

[derek-rein]

I use the following for unix timestamps on sqlite

@Column({ type: "int", default: () => "strftime('%s', 'now')"})
created_at: number

[alifarukm]

Here my solution;

import {
  Column,
  PrimaryGeneratedColumn,
  BeforeUpdate,
  BeforeInsert,
  BeforeRemove,
} from 'typeorm';

export class AppEntity {
  @PrimaryGeneratedColumn()
  id: number;

  @Column({ type: 'int', width: 11, nullable: true, readonly: true })
  deleted_at: number;

  @Column({ type: 'int', width: 11, nullable: false, readonly: true })
  created_at: number;

  @Column({ type: 'int', width: 11, nullable: true })
  updated_at: number;

  @BeforeUpdate()
  public setUpdatedAt() {
    this.updated_at = Math.floor(Date.now() / 1000);
  }

  @BeforeInsert()
  public setCreatedAt() {
    this.created_at = Math.floor(Date.now() / 1000);
  }

  @BeforeRemove()
  public setDeletedAt() {
    this.deleted_at = Math.floor(Date.now() / 1000);
  }
}