Custom precision on CreateDateColumn and UpdateDateColumn

[ghost]

MySQL 5.6 and later releases provide support for fractional timestamps via the DATETIME(1) to DATETIME(6) column types. It would be nice for TypeORM to support them on CreateDateColumn and UpdateDateColumn decorators:

@CreateDateColumn('datetime(6)')  // does not work because default has to be CURRENT_TIMESTAMP(6) instead of CURRENT_TIMESTAMP
createdAt: Date;

[pleerock]

as of 0.1.0-alpha.29 created date and updated date use datetime(6) as default data types.

[ruimgoncalves]

This breaks support for MySql 5.5 when creating a table since this version does not support specifying precision for DateTime or Timestamp types.

[daggett206]

the same problem for mysql 5.5
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near \'(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6)\' at line 1'

[svicalifornia]

@pleerock Reopen this issue and fix for MySQL 5.5.

[pleerock]

in latest @next you are able to do following:

@CreateDateColumn({ precision: 4 /* or any other */, default: () => "CURRENT_TIMESTAMP" })

To make this functionality to work on old mysql versions.

[svicalifornia]

@pleerock This issue was reopened because the addition of precision caused MySQL 5.5 to fail when creating a table. Since this has now been closed twice, perhaps someone should open a new issue for the MySQL 5.5 compatibility problem.

@ruimgoncalves or @daggett206 Would either of you like to open a new issue for the MySQL 5.5 precision problem?

[pleerock]

@svicalifornia I wrote a fix for Mysql 5.5. above (set custom default and precision)

[svicalifornia]

@pleerock Sorry, I'm trying to understand how you have resolved the MySQL 5.5 issue raised by @ruimgoncalves or @daggett206 above.

Last July, you said that the new default for CreateDateColumn and UpdateDateColumn was changed to DATETIME(6). Then the others posted that MySQL 5.5 doesn't support any precision for DATETIME:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near \'(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6)\' at line 1'

If you have fixed this, does that mean that you changed the default from DATETIME(6) back to simply DATETIME with no precision?

[AlexMesser]

@svicalifornia in 5.7 we create DATETIME with precision of 6 to make it more accurate. For backward compatibility with 5.5 I add in typeorm@0.2.0-alpha.42 support for precision: null which means that DATETIME or another date type column will be created without any precision.

@CreateDateColumn({ precision: null, type: "timestamp", default: () => "CURRENT_TIMESTAMP" })
createDate: Date;

[agborkowski]

each time when i run schema:sync, log sqls, type orm is generating same updates

little bit different for migration

dbtype:mariadb i tried drop database, recovery from classes and still same diffs ;( for default entities

    @CreateDateColumn()
    createdAt: Date;

    @UpdateDateColumn()
    updatedAt: Date;
}

i know it isn't correlated with datetime topic but as u see on the first screen for the process_id key its same problem
sync and migration diffs appear

[TheRealAmir]

@svicalifornia in 5.7 we create DATETIME with precision of 6 to make it more accurate. For backward compatibility with 5.5 I add in typeorm@0.2.0-alpha.42 support for precision: null which means that DATETIME or another date type column will be created without any precision.

@CreateDateColumn({ precision: null, type: "timestamp", default: () => "CURRENT_TIMESTAMP" })
createDate: Date;

Hi Alex
while your soultion fixed the problem for CreateDateColumn, UpdateDateColumn still suffers from the same problem.

I have used it like so:
@UpdateDateColumn({ precision: null, type: "timestamp", default: () => "CURRENT_TIMESTAMP" }) createdAt: Date;

Am I missing something or this is still an issue?
Thanks

[kelly-tock]

I am on mariadb 10.4.11, am only able to use the query builder directly to get this column working. I am unable to just say something like

User.create(....).save()

as I will get an error like this:

You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'Oct 07 2020 07:32:35 GMT-0500 (Central Daylight Time), Wed Oct 07 2020 07:32:35 ' at line 1

entity:

@ObjectType()
@Entity()
export class User extends BaseEntity {
	@Field(() => Int)
	@PrimaryGeneratedColumn()
	id!: number;

	@Field(() => String)
	@CreateDateColumn()
	createdAt = Date;

	@Field(() => String)
	@UpdateDateColumn()
	updatedAt = Date;

	@Field()
	@Column({ unique: true })
	username!: string;

	@Field()
	@Column({ unique: true })
	email!: string;

	@Column()
	password!: string;
}

tried the above workarounds as well.

but if I do this:

const result = await getConnection()
				.createQueryBuilder()
				.insert()
				.into(User)
				.values({
					username: options.username,
					password: hashedPassword,
					email: options.email,
				})
				.execute();

it is working.

on the latest version of type orm.

[gabbrieu]

How can I set precision null nowadays with EntitySchema? The field currently is accepting only number and undefined, not null. I can do precision: null as any but it's a workaround, not the best solution.

Also, the updatedAt field with default: () => 'CURRENT_TIMESTAMP' is generating CURRENT_TIMESTAMP(6), but the createdAt field not.