Repository: rickbergfalk/sqlpad
Pull request #430 (2019-05-15 04:07:58)

Display ISO strings in result grid (Remove moment from client)
At one point I was using moment to show a friendly date of a query created/last updated. I'm not doing that anymore, and the only place moment was used on the front end was for string formatting. It isn't all that useful though, as SQLPad displays the date as UTC (without the Z). 

(_Why it does that regardless of whether the date is actually UTC in the database is a whole other thing. As far as JS is concerned the date value is UTC, and its what we also want to display to the end user... it works out somehow. The state of data typing and display is kind of a mess but it is too late to change it_)

Because we're showing UTC dates anyways, we can just use the ISO string. It's probably a better more universal way to display the data anyways, and it is probably what the database wants in SQL. Doing this also allows us to remove moment.js, shaving off 50 KB of JS or so from the bundle.

Also added milliseconds back. I don't know why I removed them from the time.

Also added detection to figure out if the data type is a date or a datetime based on whether the JS date object's time is all 0s or not, per the recommendation in #399 


Patch:
diff --git a/client/package-lock.json b/client/package-lock.json
index 9fbf188b..f22032db 100644
--- a/client/package-lock.json
+++ b/client/package-lock.json
@@ -8582,11 +8582,6 @@
         }
       }
     },
-    "moment": {
-      "version": "2.24.0",
-      "resolved": "https://registry.npmjs.org/moment/-/moment-2.24.0.tgz",
-      "integrity": "sha512-bV7f+6l2QigeBBZSM/6yTNq4P2fNpSWj/0e7jQcy87A8e7o2nAfP/34/2ky5Vw4B9S446EtIhodAzkFCcR4dQg=="
-    },
     "move-concurrently": {
       "version": "1.0.1",
       "resolved": "https://registry.npmjs.org/move-concurrently/-/move-concurrently-1.0.1.tgz",
diff --git a/client/package.json b/client/package.json
index 90a62497..b1df1982 100644
--- a/client/package.json
+++ b/client/package.json
@@ -15,7 +15,6 @@
     "match-sorter": "^3.0.0",
     "mdi-react": "^5.3.0",
     "mitt": "^1.1.3",
-    "moment": "^2.24.0",
     "prop-types": "^15.7.2",
     "react": "^16.8.6",
     "react-ace": "^6.5.0",
diff --git a/client/src/common/QueryResultDataTable.js b/client/src/common/QueryResultDataTable.js
index 69140676..e7081764 100644
--- a/client/src/common/QueryResultDataTable.js
+++ b/client/src/common/QueryResultDataTable.js
@@ -5,15 +5,18 @@ import throttle from 'lodash/throttle';
 import Draggable from 'react-draggable';
 import Measure from 'react-measure';
 import SpinKitCube from './SpinKitCube.js';
-import moment from 'moment';
 
 const renderValue = (input, fieldMeta) => {
   if (input === null || input === undefined) {
     return <em>null</em>;
   } else if (input === true || input === false) {
     return input.toString();
+  } else if (fieldMeta.datatype === 'datetime') {
+    // Remove the letters from ISO string and present as is
+    return input.replace('T', ' ').replace('Z', '');
   } else if (fieldMeta.datatype === 'date') {
-    return moment.utc(input).format('MM/DD/YYYY HH:mm:ss');
+    // Formats ISO string to YYYY-MM-DD
+    return input.substring(0, 10);
   } else if (typeof input === 'object') {
     return JSON.stringify(input, null, 2);
   } else {
diff --git a/client/src/common/getTauChartConfig.js b/client/src/common/getTauChartConfig.js
index 7ac43fca..51103c08 100644
--- a/client/src/common/getTauChartConfig.js
+++ b/client/src/common/getTauChartConfig.js
@@ -75,7 +75,7 @@ export default function getTauChartConfig(
     const newRow = {};
     Object.keys(row).forEach(col => {
       const datatype = queryResult.meta[col].datatype;
-      if (datatype === 'date') {
+      if (datatype === 'date' || datatype === 'datetime') {
         newRow[col] = new Date(row[col]);
       } else if (datatype === 'number') {
         newRow[col] = Number(row[col]);
diff --git a/server/lib/getMeta.js b/server/lib/getMeta.js
index a30f0d05..70e79bc5 100644
--- a/server/lib/getMeta.js
+++ b/server/lib/getMeta.js
@@ -49,10 +49,18 @@ module.exports = function getMeta(rows) {
         return;
       }
 
-      // if we don't have a data type and we have a value yet lets try and figure it out
+      // If we don't have a data type and we have a value yet lets try and figure it out
+      // For js date object, if there are all zeros for time we'll make assumptions that this is intended as date, not datetime
+      // Ideally this should come from database result schema, but not all drivers have that and it'd be a lot of work to take on at this point
       if (!meta[key].datatype) {
         if (_.isDate(value)) {
-          meta[key].datatype = 'date';
+          const dt = new Date(value);
+          const isoString = dt.toISOString();
+          if (isoString.includes('T00:00:00.000Z')) {
+            meta[key].datatype = 'date';
+          } else {
+            meta[key].datatype = 'datetime';
+          }
         } else if (isNumeric(value)) {
           meta[key].datatype = 'number';
         } else if (_.isString(value)) {
@@ -60,6 +68,18 @@ module.exports = function getMeta(rows) {
         }
       }
 
+      // If the datatype is date, we should check to see if it changes to datetime
+      // The distinction between these are:
+      //   * dates will have ISO strings with times of all zeros
+      //   * datetimes will have ISO strings with times
+      // If all values have 0s for times, we'll assume a date type
+      if (meta[key].datatype === 'date' && _.isDate(value)) {
+        const dt = new Date(value);
+        if (!dt.toISOString().includes('T00:00:00.000Z')) {
+          meta[key].datatype = 'datetime';
+        }
+      }
+
       // if the datatype is number-like,
       // we should check to see if it ever changes to a string
       // this is hacky, but sometimes data will be
@@ -99,7 +119,10 @@ module.exports = function getMeta(rows) {
         }
       }
 
-      if (meta[key].datatype === 'date' && _.isDate(value)) {
+      if (
+        (meta[key].datatype === 'date' || meta[key].datatype === 'datetime') &&
+        _.isDate(value)
+      ) {
         // if we haven't yet defined a max and this row contains a number
         if (!meta[key].max) {
           meta[key].max = value;


Test patch:
diff --git a/server/test/lib/getMeta.js b/server/test/lib/getMeta.js
index 492f253e..a62349f1 100644
--- a/server/test/lib/getMeta.js
+++ b/server/test/lib/getMeta.js
@@ -3,6 +3,7 @@ const getMeta = require('../../lib/getMeta.js');
 
 const d1 = new Date();
 const d2 = new Date(new Date().getTime() + 60000);
+const noTime = new Date('2019-01-01T00:00:00.000Z');
 
 describe('lib/getMeta.js', function() {
   it('returns expected results', function() {
@@ -14,8 +15,9 @@ describe('lib/getMeta.js', function() {
         decimalString: null,
         number: null,
         string: null,
-        date: null,
-        numberString: null
+        datetime: null,
+        numberString: null,
+        date: noTime
       },
       {
         alwaysNull: null,
@@ -23,8 +25,9 @@ describe('lib/getMeta.js', function() {
         decimalString: '0.999',
         number: 30,
         string: 'abcdefg',
-        date: d2,
-        numberString: 100
+        datetime: d2,
+        numberString: 100,
+        date: null
       },
       {
         alwaysNull: null,
@@ -32,8 +35,9 @@ describe('lib/getMeta.js', function() {
         decimalString: '0.111',
         number: 0,
         string: '0',
-        date: d1,
-        numberString: 0
+        datetime: d1,
+        numberString: 0,
+        date: noTime
       },
       {
         alwaysNull: null,
@@ -41,8 +45,9 @@ describe('lib/getMeta.js', function() {
         decimalString: null,
         number: null,
         string: 'abc',
-        date: null,
-        numberString: null
+        datetime: null,
+        numberString: null,
+        date: noTime
       }
     ];
 
@@ -68,12 +73,14 @@ describe('lib/getMeta.js', function() {
     assert.equal(meta.string.datatype, 'string', 'string.datatype');
     assert.equal(meta.string.maxValueLength, 7, 'string.maxValueLength');
 
-    assert.equal(meta.date.datatype, 'date', 'date.datatype');
-    assert.equal(meta.date.max.getTime(), d2.getTime(), 'date.max');
-    assert.equal(meta.date.min.getTime(), d1.getTime(), 'date.min');
+    assert.equal(meta.datetime.datatype, 'datetime', 'datetime.datatype');
+    assert.equal(meta.datetime.max.getTime(), d2.getTime(), 'datetime.max');
+    assert.equal(meta.datetime.min.getTime(), d1.getTime(), 'datetime.min');
 
     assert.equal(meta.numberString.datatype, 'number', 'numberString.datatype');
     assert.equal(meta.numberString.max, 100, 'numberString.max');
     assert.equal(meta.numberString.min, 0, 'numberString.min');
+
+    assert.equal(meta.date.datatype, 'date', 'date.datatype');
   });
 });