Sequelize: Custom Date Format in SQLite Database-CodePudding

I have a column in an SQLite database with a DATE Sequelize type:

modified:{ type: DataTypes.DATE }

Just before saving an object, I get the date for "now" like this:

object.modified = new Date()

await MyType.upsert(object)

Everything looks good up until here, but when I view the data in an SQLite viewer app, I see this date format:

//JavaScript date Format in SQLite
2022-06-14 18:52:44.816  00:00

...but in order for it to be compatible with my corresponding Swift app, I need it to be in this format:

//Swift date format in SQLite
2022-06-14T18:52:44.816

The only way I have found to get the same format in my JS app is to save the date as a string with Day.js like this: dayjs(new Date()).format('YYYY-MM-DD[T]HH:mm:ss[.]SSS').

How can I have a custom date format for a Sequelize DATE in SQLite?

CodePudding user response：

I started writing my question last night and came up with a potential solution today, so I thought I'd share it. It may not be the right way to do this, but it seems to work.

In order to have a custom format for my modified date column, I can use a Sequelize getter and setter in my data type definitions.

import { Sequelize, DataTypes, Model } from 'sequelize'
import dayjs from 'dayjs'

modified: { 
  type: DataTypes.STRING,
  //Whenever modified is retrieved from the DB, format the string to a date object
  get(){
    return dayjs(this.getDataValue('modified'), 'YYYY-MM-DD[T]HH:mm:ss[.]SSS')
  },
  //Whenever modified is saved, format the date object to my custom string format
  set(value){
    this.setDataValue('modified', dayjs(value).format('YYYY-MM-DD[T]HH:mm:ss[.]SSS'))
  }
}

CodePudding user response：

Sequelize's DATE datatype defaults to that date time offset format. If you have the date format that you want available to you in the database software (Either out of the box or custom-created), you can extend Sequelize's datatypes. It's relatively painless code to implement.