sqlitenestjstypeorm

Sqlite datetime saves one day less than the original input


TypeORM entity:

import { Column, Entity, PrimaryGeneratedColumn } from 'typeorm';

@Entity()
export class User {
  @PrimaryGeneratedColumn('uuid')
  id: string;

  @Column({
    type: 'nvarchar',
    length: 50,
    nullable: false,
    unique: true
  })
  email: string;

  @Column({
    type: 'nvarchar',
    length: 30,
    nullable: false,
  })
  password: string;

  @Column({
    type: 'nvarchar',
    length: 50,
    nullable: false
  })
  fullName: string

  @Column({
    type: 'datetime',
    nullable: false
  })
  dateOfBirth: Date
}

DTO:

import { IsDate, IsEmail, IsNotEmpty, IsOptional, IsString } from 'class-validator';

export class CreateUserDto {
  @IsEmail()
  @IsNotEmpty()
  email: string;

  @IsString()
  @IsNotEmpty()
  password: string;

  @IsString()
  @IsNotEmpty()
  fullName: string;

  @IsString()
  @IsNotEmpty()
  dateOfBirth: string;
}

Service:

  async createUserAsync(user: CreateUserDto) {

    const users = await this.dataService.users.findOneBy({
      email: user.email,
    });
    if (users) {
      throw new BadRequestException(`Email in use email:${user.email}`);
    }

    const salt = randomBytes(8).toString('hex');
    const hash = (await this._scrypt(user.password, salt, 32)) as Buffer;

    const result = salt + '.' + hash.toString('hex');

    user.password = result;

    user.dateOfBirth = new Date(user.dateOfBirth).toISOString();
    const createdUser = await this.dataService.users.create(user);
    const savedUser = await this.dataService.users.save(createdUser);

    return savedUser;
  }

payload: { fullName:'xxx', email:'a@a.com', password: '1234', dateFfBith: '9/4/2024' }

saved data: { fullName:'xxx', email:'a@a.com', password: '1234', dateOfBith: '9/3/2024' // one day less }

Payload contains the original date that the client provided, however, when it saves in the sqlite db, it saves one day less than the original provided date.


Solution

  • To save date properly in sqlite, you need to send the data without timezone info.

        const formSubmitAction = (values: any) => {
            form.resetFields();
            if(isExpense){
                values.expenseDate =  moment(values.date.toISOString()).format('YYYY-MM-DD');
            }else{
                values.depositDate = moment(values.date.toISOString()).format('YYYY-MM-DD');
            }
            delete values['date'];
            submitFormAction(values);
        }
    
    

    Here I'm using momentjs to remove the timezone info from the date.