Currently, I am using Knex.js to retrieve records from a SQLite database. My goal is to then convert these records into JSON as part of a web api. As it stands, the dates retrieved from SQLite are in the format 2018-01-01 00:00:00.000
Instead, I would like them to be in the format that JavaScript's date toJSON() function produces: 1975-08-19T23:15:30.000Z
Is there a method available to have the database automatically format the date fields in this manner when querying it?
Is there a way to instruct Sqlite on how to format dates in a connection string or Knex?
In brief: Unfortunately, no.
Sqlite stores ISO8601 dates as strings and does not offer different data types for date formatting.
According to the SQLite Documentation:
SQLite does not have dedicated storage classes for dates and times. Instead, SQLite's Date And Time Functions can store dates and times as TEXT, REAL, or INTEGER values:
TEXT represents dates as ISO8601 strings ("YYYY-MM-DD HH:MM:SS.SSS").
REAL signifies Julian day numbers, counting days since noon in Greenwich on November 24, 4714 B.C. based on the Gregorian calendar.
INTEGER denotes Unix Time, recording seconds since 1970-01-01 00:00:00 UTC.
Possible Solution #1 - within the database:
One workaround could be using strftime() in a CREATE TRIGGER BEFORE ... to modify the default string format. Here is a link to the documentation: Docs link.
Possible Solution #2 - in your JS code:
You may choose to pre-format the date strings before storing them using javascript (e.g., .toJSON()). An example of this approach could be:
var createDate = (new Date()).toISOString();
This will format your dates for storage with the desired structure such as: '2018-01-28T02:39:53.226Z'.
Additionally, be cautious when retrieving an ISO8601 string date 'type' from the SQLite DB through Knex, as it will return as a string type instead of a Date() type.
I hope this information proves useful. Regards, Gary.
Although it may seem complex, it is entirely feasible - here's a clue or idea to consider. Feel free to concatenate or expand upon it as needed.
Here is a SQL statement for reference:
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;
Here is the output of the query:
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 on: 02/23/2022
Recently, I attempted to create a fullscreen webpage like this one: https://i.sstatic.net/21KCr.png My goal was to have the color of each element change randomly every second. Here is the JavaScript code I implemented: <script> var tid = se ...
I've integrated the LineControl Editor into my app and everything is functioning perfectly, except for when I attempt to insert text into the editor. Here's the link to the LineControl GitHub page: https://github.com/suyati/line-control/wiki Fo ...
I am struggling to understand how Bootstrap darkens the background of a page when a modal is displayed. Even though a class is added to the body tag (modal-open), the effect on the background isn't clear to me. Learn more about Bootstrap modals here ...
Just dipping my toes into AngularJS waters, and this question will surely prove that. We're in the midst of revamping an existing app to take advantage of AngularJS for CRUD operations and more. Rather than starting from scratch, we've decided a ...
I have an array that looks like this: data = [ {name:'Tom', a:1, b:1, c:0}, {name:'Sam', a:0, b:1, c:1}, {name:'Tom', a:0, b:0, c:1}, {name:'Sam', a:1, b:2, c:1}, {name: 'Jack', a:1, b:2, c:0} ] ...
Searching for a way to endlessly call functions with promises. Experimented with 2 scenarios, one successful and the other not so much. The goal of the code that failed is to retrieve data from an API and store it in a database. Currently learning about p ...
I created a custom contextmenu directive using AngularJs. However, I am facing an issue where the menu is supposed to close when I click anywhere on the page except for the custom menu itself. Although I have added a click function to the document to achie ...
I am facing a challenge where I need to ensure that the camera stays at a fixed distance behind an object as it moves and rotates. Specifically, I am working on drawing an airplane and require the camera to always focus on its rear end, while allowing user ...
My attempts to set up React have been unsuccessful so far. Initially, I tried installing it using the command npx create-react-app ./, which did not work. Then, I attempted to run npm init react-app my-app, but unfortunately, that also failed. The error me ...
At this moment, executing the command npm install will download the latest versions of packages. Is there a way to install the exact versions specified in the package.json file? ...
Can a html value be permanently modified using javascript? I am trying to edit a local file. Below are the codes I'm using: function switchPic(){ top.topPage.activeDef = top.topPage.document.getElementById('h1'); top.topPage.activeDef. ...
While developing a chat app, I encountered an issue with CSS styling. I assigned a specific class to incoming messages and set a max-width limit for the container element. However, instead of the width adjusting based on the content within it, the message ...
Suppose I have two overlapping divs, along with the following jQuery code snippet: $( "#div1" ).click(function() { console.log('click on div1'); }); $( "#div2" ).mousemove(function() { console.log('mousemove on div2'); }); From w ...
This issue remains unresolved.. it has simply been avoided.. so do not rely on this as a reference Here is what I have: <div> <img onclick='this.style.display="none";this.parentNode.getElementsByTagName("div")[0].style.display="block";t ...
When I map over an array of books to display the titles in a list, I encounter an issue with the initial empty string value for the title. This causes the list to render an empty item initially. Below is my book array: @observable books = [ {title:" ...
I am in search of a formal method to determine the hexadecimal color based on a percentage value of relative humidity. Here is an illustration of the color range I aim to utilize, it being the most widely used. Is there a specific technique that I can em ...
While this question may seem basic, I am having trouble understanding the behavior described. I have written some JavaScript code and I am puzzled why the second call to foo does not work. You can find the code in this JSFiddle link. $.fn.foo = function( ...
Given that pushAsset prohibits loading external resources, I am interested in displaying this template/script from my NPM module: views/tag.html <script async src="https://www.googletagmanager.com/gtag/js?id={{ data.gid }}"></script> NPM mod ...
I have a button that successfully deletes an image name from a mySQL table. However, I also want it to delete the actual image from the server. Below is the code snippet from my index.js: document.querySelector('table tbody').addEventListener(&a ...
Within my application, I have implemented an input field and a reference to store the user input value. Additionally, there are several React Links within a div that provide recommended keywords for users to search. I am seeking a way to dynamically modif ...