typeorm: Cache breaks date field

Issue type: [V] bug report

Database system/driver: [V] mysql / mariadb

TypeORM version: [V] 5.2.0

Steps to reproduce: When using cache on table that have date field, it returns date with time.

Entity example:

@Entity()
export class RateMinute {
  @Index('IDX_symbol')
  @PrimaryColumn()
  symbol: string;

  @Column(decimal)
  open: number;

  @Column(decimal)
  close: number;

  @Column(decimal)
  high: number;

  @Column(decimal)
  low: number;

  @Column(decimal)
  volume: number;

  @Index('IDX_date')
  @PrimaryColumn('date')
  date: string;

  @Index('IDX_time')
  @PrimaryColumn('time')
  time: string;
}
CREATE TABLE `rate_minute` (
  `symbol` varchar(255) NOT NULL,
  `open` decimal(20,8) NOT NULL,
  `close` decimal(20,8) NOT NULL,
  `high` decimal(20,8) NOT NULL,
  `low` decimal(20,8) NOT NULL,
  `volume` decimal(20,8) NOT NULL,
  `date` date NOT NULL,
  `time` time NOT NULL,
  PRIMARY KEY (`symbol`,`date`,`time`),
  KEY `IDX_symbol` (`symbol`),
  KEY `IDX_date` (`date`),
  KEY `IDX_time` (`time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

No cache result:

{
symbol: "USDJPY",
open: "111.57900000",
close: "111.58400000",
high: "111.58400000",
low: "111.57900000",
volume: "4.93080000",
date: "2018-09-13",
time: "11:15:00"
}

Cache result (see date):

{
symbol: "USDJPY",
open: "111.57900000",
close: "111.58400000",
high: "111.58400000",
low: "111.57900000",
volume: "4.93080000",
date: "2018-09-12T21:00:00.000Z",
time: "11:15:00"
}

About this issue

  • Original URL
  • State: open
  • Created 8 years ago
  • Reactions: 9
  • Comments: 18 (1 by maintainers)

Commits related to this issue

Most upvoted comments

Is there any news on the topic? I have the same issue as described by @leonidasv but I have noticed that the problem occurs only when the column type is specified. This works:

  @UpdateDateColumn()
  updatedAt!: Date;

but this doesn’t:

  @UpdateDateColumn({ type: "timestamptz" })
  updatedAt!: Date; // resolves to string from cache

@iago-f-s-e , I use the solution above

During my research, I read the types, and I found :

export type ColumnType = WithPrecisionColumnType | WithLengthColumnType | WithWidthColumnType | SpatialColumnType | SimpleColumnType | BooleanConstructor | DateConstructor | NumberConstructor | StringConstructor;

Specially : DateConstructor

I don’t know how it really works internally, but I suppose the conversion from database date type to redis is converted with the implementation of DateConstructor, so the time is added to the date 😡.

A solution to work with it is to convert date before returning it to JSON to the client.

In my case :

@Column({
  nullable: true,
  type: 'date',
  transformer: {
    from(value: Date): string {
      return moment(value).format('YYYY-MM-DD');
    },
    to(value: Date): string {
      return moment(value).format('YYYY-MM-DD');
    },
  },
})
dt: Date;

A date is a date wherever you are in earth. The 1 April is the same in Paris, New York or Pekin. We are not working with hours so the conception of timezone wouldn't be a problem.

But there is a requirement that : 

- be sure that your client is sending you date as string (YYYY-MM-DD)
- keep your database as UTC
- keep redis with the same timezone as your database (UTC)
- keep your backend with the same timezone (UTC)

Hope it help 👍