alasql: DATE function not working

the DATE function is not working as expected

> alasql('CREATE TABLE t (id BIGINT, dt DATE)')
1
> alasql('INSERT INTO t (1, NOW())')
1
> alasql('INSERT INTO t (1, "2022-01-30 01:00:00")')
1
> alasql('SELECT * FROM t')
[
  { id: 1, dt: '2023-01-30 15:22:41.470' },
  { id: 1, dt: '2022-01-30 01:00:00' }
]

in general, timestamp / date objects don’t work as expected (we’ve overridden function slike NOW, DATE_ADD etc for this)

however DATE itself seems to not throw an error but overriding also doesn’t work…

> alasql('SELECT DATE(dt) FROM t')                             
[                                                        
  { 'DATE(dt)': 2023-01-30T06:22:41.470Z },                      
  { 'DATE(dt)': 2022-01-29T16:00:00.000Z }
]                                       

I’ve tried overriding DATE for this but this throws a confusing error

> const alasql = require('alasql');
undefined
> const moment = require('moment');
undefined
> alasql('CREATE TABLE t (id BIGINT, dt DATE)')
1
> const DATE_FORMAT = 'YYYY-MM-DD';
undefined
> alasql.fn.DATE = (dt) => moment(dt).format(DATE_FORMAT);
[Function (anonymous)]
> alasql.exec('INSERT INTO t (id, dt) values (1, "2022-01-30 01:00:00")')
Uncaught ReferenceError: DATE is not defined
    at yy.Insert.eval (eval at yy.Insert.compile (/home/mark/work/gopax/node_modules/alasql/dist/alasql.fs.js:14433:18), <anonymous>:3:67)
    at statement (/home/mark/work/gopax/node_modules/alasql/dist/alasql.fs.js:14493:14)
    at Function.alasql.dexec (/home/mark/work/gopax/node_modules/alasql/dist/alasql.fs.js:4682:11)
    at Function.alasql.exec (/home/mark/work/gopax/node_modules/alasql/dist/alasql.fs.js:4664:17)

(consistent regardless of whether DATE is overriden before the DDL or after)

> const alasql = require('alasql');
undefined
> const moment = require('moment');
undefined
> const DATE_FORMAT = 'YYYY-MM-DD';
undefined
> alasql.fn.DATE = (dt) => moment(dt).format(DATE_FORMAT);
[Function (anonymous)]
> alasql('CREATE TABLE t (id BIGINT, dt DATE)')
1
> alasql.exec('INSERT INTO t (id, dt) values (1, "2022-01-30 01:00:00")')
Uncaught ReferenceError: DATE is not defined
    at yy.Insert.eval (eval at yy.Insert.compile (/home/mark/work/gopax/node_modules/alasql/dist/alasql.fs.js:14433:18), <anonymous>:3:67)
    at statement (/home/mark/work/gopax/node_modules/alasql/dist/alasql.fs.js:14493:14)
    at Function.alasql.dexec (/home/mark/work/gopax/node_modules/alasql/dist/alasql.fs.js:4711:28)
    at Function.alasql.exec (/home/mark/work/gopax/node_modules/alasql/dist/alasql.fs.js:4664:17)

node version: v14.17.3 alasql version: 2.5.3

About this issue

  • Original URL
  • State: open
  • Created 4 years ago
  • Comments: 21 (14 by maintainers)

Most upvoted comments

That is the same code, as it’s javascript either run in the browser or in nodejs. Thus, both environments will work if you have a fix for the issue at hand. @mathewalexKerala

@mathiasrw @mathewalexKerala can you please make sure that when using alasql.options.dateAsString=false, it remains to work as is? We use dates a lot with that setting, and have no issues at all (as long as JS dates are used instead of string values).

sure

I want to work on this issue , if nobody is doing it

If the issue is still not fixed, I 'd like to contribute. @mathiasrw

Hey there, i’m interested in this issue will you please assign this to me.

in sql, these two should return as the same data type but it turns out the returned data types are different

I see. Yes. Hmm. This is a good point. This is not good. Ill see what I can do.

general, timestamp / date objects don’t work as expected (we’ve overridden function slike NOW, DATE_ADD etc for this)

Interesting. Do you mind sharing the functions so we can improve this for the community?

I’m currently using alasql for semi-unit testing sql queries so I’ve only fixed it to the point that it works for us

const DATE_FORMAT = 'YYYY-MM-DD';
const DATE_TIME_FORMAT = `${DATE_FORMAT} HH:mm:ss`;

alasql.fn.CURRENT_TIMESTAMP = alasql.fn.NOW = () => moment()
  .utc()
  .format(DATE_TIME_FORMAT);
alasql.fn.DATE_SUB = (dt, interval) => moment(dt)
  .subtract(interval, 'milliseconds')
  .format(DATE_TIME_FORMAT);
alasql.fn.DATE_ADD = (dt, interval) => moment(dt)
  .add(interval, 'milliseconds')
  .format(DATE_TIME_FORMAT);
alasql.fn.UNIX_TIMESTAMP = (dt) => moment(dt, DATE_TIME_FORMAT).unix();

the overrides also use moment which I’m not sure alasql would want just for datetime functionality

general, timestamp / date objects don’t work as expected (we’ve overridden function slike NOW, DATE_ADD etc for this)

Interesting. Do you mind sharing the functions so we can improve this for the community?

image The problem is the time part of the field as its a date - right?