What is the proper way to use the `Sequelize.fn` method to format a SQLite3 datetime?

user_2045696 0
Viewed 2638

I'm experimenting with Sequelize.js using both a MySQL and a SQLite3 database and displaying the data in JSON format in the console. With MySQL, the Sequelize.fn function enables me to control the format of the createdAt and updatedAt fields like this:

[Sequelize.fn('date_format', Sequelize.col('updatedAt'), '%m-%d-%Y %H:%i:%s'), 'updatedAt']

For example:

Article.findAll({
    attributes: [
        'id',
        'title',
        'body',
        [Sequelize.fn('date_format', Sequelize.col('createdAt'), '%m-%d-%Y %H:%i:%s'), 'createdAt'],
        [Sequelize.fn('date_format', Sequelize.col('updatedAt'), '%m-%d-%Y %H:%i:%s'), 'updatedAt']
    ]
})
    .then(tasks => {
        console.log(JSON.stringify(tasks, null, 2))
    })

returns this:

  {
    "id": 27,
    "title": "This is a test title",
    "body": "The main body of the article appears here.",
    "createdAt": "05-28-2017 23:41:42",
    "updatedAt": "05-28-2017 23:41:42"
  }

SQLite, however, does not recognize the date_format function and throws an error:

Unhandled rejection SequelizeDatabaseError: SQLITE_ERROR: no such function: date_format

Research suggests that the correct SQLite function is strftime, but although no error message appears, the output is null. For example, 

[Sequelize.fn('strftime', Sequelize.col('updatedAt'), '%m-%d-%Y %H:%i:%s'), 'updatedAt']

results in

{
  "id": 42,
  "title": "Hello world!",
  "body": "This is stored in SQLite!",
  "createdAt": "2017-05-28T23:19:41.738Z",
  "updatedAt": null
}

Can anyone point me in the right direction?

sequelize.js sqlite
1 Answers

I had the same problem. You were almost there. Only the parameters to Sequelize.fn were in the wrong order.

with

[Sequelize.fn('strftime', '%m-%d-%Y %H:%i:%s', Sequelize.col('updatedAt')), 'updatedAt']

your date should be properly formatted.

Thanks for pointing me in the right direction!

user_60302730
Related