Fix sqlite date functions for epoch (brianc#319)

barryhammen · brianc · commit bc4d8d9dd00c · 2016-05-26T12:14:28.000-05:00
diff --git a/lib/dialect/sqlite.js b/lib/dialect/sqlite.js
@@ -81,7 +81,13 @@ Sqlite.prototype.visitFunctionCall = function (functionCall) {
         format = "'%H'";
         break;
     }
-    var txt = 'strftime(' + format + ', ' + (nodes[0] + '') + ')';
+    var col = (nodes[0] + '');
+    if (_this.config.dateTimeMillis) {
+      // Convert to a datetime before running the strftime function
+      // Sqlite unix epoch is in seconds, but javascript is milliseconds.
+      col = 'datetime(' + col + '/1000, "unixepoch")';
+    }
+    var txt = 'strftime(' + format + ', ' + col + ')';
     return txt;
   }
 
diff --git a/test/dialects/date-tests.js b/test/dialects/date-tests.js
@@ -36,8 +36,11 @@ Harness.test({
     string: 'SELECT EXTRACT(MONTH FROM "customer"."metadata") FROM "customer"'
   },
   sqlite: {
-    text  : 'SELECT strftime(\'%m\', "customer"."metadata") FROM "customer"',
-    string: 'SELECT strftime(\'%m\', "customer"."metadata") FROM "customer"'
+    text: 'SELECT strftime(\'%m\', datetime("customer"."metadata"/1000, "unixepoch")) FROM "customer"',
+    string: 'SELECT strftime(\'%m\', datetime("customer"."metadata"/1000, "unixepoch")) FROM "customer"',
+    config: {
+      dateTimeMillis: true
+    }
   },
   mysql: {
     text  : 'SELECT MONTH(`customer`.`metadata`) FROM `customer`',
@@ -86,8 +89,11 @@ Harness.test({
     string: 'SELECT EXTRACT(HOUR FROM "customer"."metadata") FROM "customer"'
   },
   sqlite: {
-    text  : 'SELECT strftime(\'%H\', "customer"."metadata") FROM "customer"',
-    string: 'SELECT strftime(\'%H\', "customer"."metadata") FROM "customer"'
+    text: 'SELECT strftime(\'%H\', datetime("customer"."metadata"/1000, "unixepoch")) FROM "customer"',
+    string: 'SELECT strftime(\'%H\', datetime("customer"."metadata"/1000, "unixepoch")) FROM "customer"',
+    config: {
+      dateTimeMillis: true
+    }
   },
   mysql: {
     text  : 'SELECT HOUR(`customer`.`metadata`) FROM `customer`',

Original file line number	Diff line number	Diff line change
`@@ -81,7 +81,13 @@ Sqlite.prototype.visitFunctionCall = function (functionCall) {`
`81`	`81`	`format = "'%H'";`
`82`	`82`	`break;`
`83`	`83`	`}`
`84`		`- var txt = 'strftime(' + format + ', ' + (nodes[0] + '') + ')';`
	`84`	`+ var col = (nodes[0] + '');`
	`85`	`+ if (_this.config.dateTimeMillis) {`
	`86`	`+ // Convert to a datetime before running the strftime function`
	`87`	`+ // Sqlite unix epoch is in seconds, but javascript is milliseconds.`
	`88`	`+ col = 'datetime(' + col + '/1000, "unixepoch")';`
	`89`	`+ }`
	`90`	`+ var txt = 'strftime(' + format + ', ' + col + ')';`
`85`	`91`	`return txt;`
`86`	`92`	`}`
`87`	`93`