I'm using Knex.js to select records from a SQLite database. I then wish to return the records as JSON as part of a web api. When selecting dates form Sqlite they are formatted like 2018-01-01 00:00:00.000

I would like them to be formatted like JavaScript's date toJSON() function: 1975-08-19T23:15:30.000Z

Is there a way to have the database format the date fields like this when querying it?

I'm using Knex.js to select records from a SQLite database. I then wish to return the records as JSON as part of a web api. When selecting dates form Sqlite they are formatted like 2018-01-01 00:00:00.000

I would like them to be formatted like JavaScript's date toJSON() function: 1975-08-19T23:15:30.000Z

Is there a way to have the database format the date fields like this when querying it?

• javascript
• sqlite
• knex.js

Share Improve this question Follow asked Jan 27, 2018 at 16:52 EmanonEmanon 92944 gold badges1111 silver badges1111 bronze badges 3

• 1 I'm posting this as a ment because I'm not familiar with Knex, but you can format dates in SQLite easily: select strftime('%Y-%m-%dT%H:%M:%fZ', 'now'); – sbooth Commented Jan 27, 2018 at 17:24
• @sbooth Thank you, that is useful info. The reason for Knex though is to write the code in a way that works across multiple databases and I'm sure strftime is specific to only SQLite, So I was hoping there's a way in a connection string, or Knex, etc. to tell Sqlite how to format the dates. – Emanon Commented Jan 28, 2018 at 1:38
• Usually this is handled in pg/node-mysql drivers, by overriding how datetime types returned from database are parsed to JavaScript values. So every time when datetime (etc.) type of is parsed by db driver, it changes format from sql -> e.g. ISO8601. I'm not sure if sqlite3 driver supports writing own parsers though (I don't know if sqlite returns datatype to the driver so that driver knows how it should parse the returned string value). – Mikael Lepistö Commented Jan 29, 2018 at 9:48

Add a ment  | 

2 Answers 2

Sorted by: Reset to default 3

So I was hoping there's a way in a connection string, or Knex, etc. to tell Sqlite how to format the dates.

Short answer: Sorry, there's not.

SQLite stores your ISO8601 dates as strings and doesn't have built-in data types to vary that.

From SQLite Documentation:

SQLite does not have a storage class set aside for storing dates and/or times. Instead, the built-in Date And Time Functions of SQLite are capable of storing dates and times as TEXT, REAL, or INTEGER values:

TEXT as ISO8601 strings ("YYYY-MM-DD HH:MM:SS.SSS").

REAL as Julian day numbers, the number of days since noon in Greenwich on November 24, 4714 B.C. according to the proleptic Gregorian calendar.

INTEGER as Unix Time, the number of seconds since 1970-01-01 00:00:00 UTC.

Possible Workaround #1 - in the database: You can probably use the strftime() noted by @sbooth in a CREATE TRIGGER BEFORE ... to override the default storage string format. Docs link (Sorry, I haven't personally done this though.)

Possible Workaround #2 - in the JS code: Use javascript (like you mentioned: .toJSON()) to pre-format the date strings in another way for storage. I use something like:

var createDate = (new Date()).toISOString();

... to format my dates for storage in the code, which has the format you are looking for: '2018-01-28T02:39:53.226Z'.

Also, a possible gotcha to watch for: When you SELECT an ISO8601 string date 'type' from the SQLite DB via Knex, it will return as a string type, not a Date() type.

Hope this is helpful. Gary.

It is cumbersome, but of course possible - here is a hint / concept. Concatenate / expand it as necessary.

SQL statement:

SELECT 
saledt, 
substr(substr('0000000000000000000000' || saledt, -22, 22),7,4) as yr, 
substr('00' || substr(saledt,1,instr(saledt,'/')-1), -2,2) as mnth, 
substr('00' || rtrim(substr(saledt,instr(saledt,'/')+1,2),'/'),-2,2) as dy 
FROM sales 
ORDER BY yr,mnth,dy;

Output:

SALEDT                    yr      mnth  dy
10/28/1988 12:00:00 AM    1988    10    28
11/1/1988 12:00:00 AM     1988    11    01
4/1/2012 12:00:00 AM      2012    04    01
7/1/2019 12:00:00 AM      2019    07    01
8/5/2019 12:00:00 AM      2019    08    05
3/3/2020 12:00:00 AM      2020    03    03

Tested: 02/23/2022