MySQL DateTime issue

[iamchathu]

Issue Description

MySQL Datetime is not saving ISO8601 Date.

Expected Behavior

Save time without issues.

Actual Behavior

Time get changed when it saved to the database.

Since it had issues saving I have tried using transformers to help but according to debug transformer to function doesn't work. See below debug query it sends iso 8601 format to the database. (Seems transformer to method is not working.)

CreatedDate and Updated Dates columns work without issues.

order_created_at and order_update_at has datetime(6) column but order_required_date has datetime column of datetime

Steps to Reproduce

1. Create a field with datetime
2. Use MySQL connector

import { Column, CreateDateColumn, Entity, PrimaryGeneratedColumn, UpdateDateColumn } from 'typeorm';
import { Field, ID, ObjectType } from 'type-graphql';
import format from 'date-fns/format';
import parseJSON from 'date-fns/parseJSON';

@ObjectType()
@Entity('app_order')
export class Order {
   @Field(() => ID)
   @PrimaryGeneratedColumn({ name: 'order_id', unsigned: true })
    id!: string;

  @Field()
  @Column('datetime', {
    name: 'order_required_date',
    transformer: {
      to: (value: Date) => format(value, 'yyyy-MM-dd HH:mm:ss'),
      from: (value: string) => parseJSON(value),
    },
  })
  requiredDateTime!: Date;

  @Field()
  @CreateDateColumn({ name: 'order_created_at' })
  createdAt!: Date;

  @UpdateDateColumn({ name: 'order_updated_at' })
  updatedDate!: Date;
}

My Environment

Dependency	Version
Operating System	MacOS Big Sur
Node.js version	v14.15.5
Typescript version	4.1.4
TypeORM version	0.2.30

MySQL 5.6.16-1~exp1 ((Ubuntu))
Type Graphql 1.1.1

Additional Context

INSERT INTO app_order(order_id, order_required_date, order_created_at, order_updated_at) VALUES (DEFAULT, ?, DEFAULT, DEFAULT) -- PARAMETERS: ["2021-02-27T05:05:00.000Z",6]

Is saved as 2021-02-27 10:35:00

MySQL datetime format is yyyy-MM-dd HH:mm:ss according to the docs.

Relevant Database Driver(s)

• aurora-data-api
• aurora-data-api-pg
• better-sqlite3
• cockroachdb
• cordova
• expo
• mongodb
• mysql
• nativescript
• oracle
• postgres
• react-native
• sap
• sqlite
• sqlite-abstract
• sqljs
• sqlserver

Are you willing to resolve this issue by submitting a Pull Request?

• Yes, I have the time, and I know how to start.
• Yes, I have the time, but I don't know how to start. I would need guidance.
• No, I don't have the time, although I believe I could do it if I had the time...
• No, I don't have the time and I wouldn't even know how to start.

[come25136]

I'm having the same problem :(

[imnotjames]

TLDR: Timezones are tough. Either set up your server timezone to match your database timezone or use a javascript library which can support timezone-enabled date objects.

MySQL Datetime is not saving ISO8601 Date.

"2021-02-27T05:05:00.000Z" is an ISO8601 date.

INSERT INTO app_order(order_id, order_required_date, order_created_at, order_updated_at) VALUES (DEFAULT, ?, DEFAULT, DEFAULT) -- PARAMETERS: ["2021-02-27T05:05:00.000Z",6]

Is saved as 2021-02-27 10:35:00

MySQL datetime format is yyyy-MM-dd HH:mm:ss according to the docs.

Per the MySQL documentation on Date and Time literals:

MySQL recognizes DATETIME and TIMESTAMP values in these formats:

As a string in either &&'YYYY-MM-DD hh:mm:ss'** or 'YY-MM-DD hh:mm:ss' format. A “relaxed” syntax is permitted here, too: Any punctuation character may be used as the delimiter between date parts or time parts. For example, '2012-12-31 11:30:45', '2012^12^31 11+30+45', '2012/12/31 113045', and '2012@12@31 11^30^45' are equivalent.

The only delimiter recognized between a date and time part and a fractional seconds part is the decimal point.

The date and time parts can be separated by T rather than a space. For example, '2012-12-31 11:30:45' '2012-12-31T11:30:45' are equivalent.

I think you need to look into your timezone settings.

2021-02-27T05:05:00.000Z is Zulu time which means that it's being sent over as UTC because that's how you've configured your server to handle dates. My best guess is that 2021-02-27 10:35:00 is in your LOCAL timezone so you're probably in IST?

[imnotjames]

Given that this is outside the scope of TypeORM I'm going to be closing this issue.