CODEWITHSUNDEEP
typescriptsqlitedatesequelize.js
Clifton Labrum
Clifton Labrum

Reputation: 14070

Sequelize: Custom Date Format in SQLite Database

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?

Upvotes: 0

Views: 1358

Answers (2)

boc4life
boc4life

Reputation: 961

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.

Upvotes: 1

Clifton Labrum
Clifton Labrum

Reputation: 14070

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'))
  }
}

Upvotes: 0

Related Questions