DATE function not working

[hjkwon0609]

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

[mathiasrw]

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?

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

[hjkwon0609]

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

[mathiasrw]

Thanks for sharing.

Do you remember why the build in function was not working for you?

[hjkwon0609]

Thanks for sharing.

Do you remember why the build in function was not working for you?

sure thing

one simple example would be

> const alasql = require('alasql');
undefined
> alasql('SELECT NOW()')
[ { 'NOW()': '2023-02-02 17:34:27.987' } ]
> alasql('SELECT DATE_SUB(NOW(), INTERVAL 9 HOUR)')
[ { "DATE_SUB(NOW(),INTERVAL(9,'hour'))": 2023-02-01T23:34:34.143Z } ]

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

I thought since my team is using the library in a way that's different from it's intention (main purpose of this library seems to be for utilizing sql inside js without the behavior needing to match up against an actual sql engine, but we use this as a faster (despite being a bit less accurate) way of unit testing our sql queries ) these discrepancies are good-to-have's but not must-have's. Considering the nature of javascript, those two seemed understandable (just wrap them inside a Date object and they would pretty much be the same thing)

[mathiasrw]

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.

[Mohit269]

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

[mathiasrw]

Sure!

[mathiasrw]

Any luck @Mohit269 ?

[mathiasrw]

Note

Database	Output
MySQL	2023-06-09 15:11:18
PostgreSQL	2023-06-09T15:11:18.000Z
Microsoft SQL Server	2023-06-09 15:11:18.123
Oracle	2023-06-09 15:11:18
IBM DB2	2023-06-09 15:11:18.123
SQLite	2023-06-09 15:13:13

[mathiasrw]

Yes. This is absolutely broken. We need to make breaking changes for the output of the date related objects. All of them.

We need a formatter that you can configure for all the date options - including the option to use the date object - (like some of them got now)

[mathiasrw]

We will be introducing a breaking change where all date data are returned and formatted the same way

@agershun and I have decided that we will default to the string format and provide an option get all raw date information as a javascript object.

@Mohit269 Let me know if you are still keen on this or would like to step off this one.

[Shibn2]

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

[paulrutter]

Do note that recently, the alasql.options.dateAsString option was introduced (defaults to true for backwards compatibility).
When set to false, both NOW and GETDATE will just return a Date object instead of a string formatted Date.

[mathiasrw]

@paulrutter Thank you for pointing this out. I have updated https://github.com/AlaSQL/alasql/wiki/Alasql-Options

@Shibn2 any progress on this?

[mathewalexKerala]

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

[mathiasrw]

@mathewalexKerala Please!

[paulrutter]

@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).

[mathiasrw]

Its a good point. We should make sure to not change any of the old tests in order to get the new things working.

[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

[mathewalexKerala]

@paulrutter @mathiasrw should I fix both the browser side and server side code ?

[paulrutter]

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