Alasql: Date error

[neomicho85]

Date without problem:
MONTH("1997-09-22") -> 09

Date with problem:
MONTH("2017-02-01") ->01 !!!!

[neomicho85]

The link to reproduce this error: http://jsfiddle.net/b94d5e3w/
Modify any date and put:
1996-01-01 or
2017-02-01
Then in the query you will see the error when perform a MONTH(date) or even a DAY(date)

[neomicho85]

This is the function

stdfn.MONTH = function(d){
var d = new Date(d);
return d.getMonth()+1;
};

maybe you can use moment.js or date.js to fix de error

[mathiasrw]

Very odd. Hmm. Looks like others are having problems with date as well:

http://stackoverflow.com/questions/2587345/why-does-date-parse-give-incorrect-results

Are you using node or browser?

Including a whole library to fix this might be having a bad effect on size of the lib. Hmm. We might be able to just include the parser.

[neomicho85]

I'm using browser. I use moment.js because I'm already using it, but of course: parse is the best option in this case. Thx

[neomicho85]

A solution after years using moment (not any more) is to remove the leading zero.
Use: 1996-1-1 instead of 1996-01-01
http://blog.dygraphs.com/2012/03/javascript-and-dates-what-mess.html

Alasql could remove the leading zeros!

[mathiasrw]

When i look at the example code on https://jsfiddle.net/b94d5e3w/ it all displays corect. Is this a locale thing in the browser?

[neomicho85]

Weird picture I'm looking in to the example at jsfiddle and the values are others...

ROOSEVELT | 1995-10-12 | 1995 | 10 | 11
-- | -- | -- | -- | --
FILLMORE | 1994-08-09 | 1994 | 8 | 8
LINCOLN | 1994-06-23 | 1994 | 6 | 22
GARFIELD | 1993-05-01 | 1993 | 4 | 30
HOOVER | 1990-04-02 | 1990 | 4 | 1
JACKSON | 1990-01-01 | 1989 | 12 | 31

As I can see, you need to add 1 day to the current date otherwise the month is incorrect.

[joearasin]

What's happening here is a time zone issue. new Date("YYYY-MM-DD") returns YYYY-MM-DD, Midnight GMT, but the Date object itself is localized. Initializing a date on YYYY-MM-DD in the Western Hemisphere will have issues with the methods as implemented. For example, I'm in NY, and I get:

new Date("2019-01-01");
> Mon Dec 31 2018 19:00:00 GMT-0500 (EST)

I think the solution is to use getUTCMonth, getUTCDate, and getUTCFullYear

d = new Date("2019-01-01")
> Mon Dec 31 2018 19:00:00 GMT-0500 (EST)
d.getMonth()
> 11
d.getUTCMonth()
> 0
d.getDate()
> 31
d.getUTCDate()
> 1
d.getFullYear()
> 2018
d.getUTCFullYear()
> 2019

[joearasin]

Also worth noting: https://developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Global_Objects/Date/parse

Given a non-standard date string of "March 7, 2014", parse() assumes a local time zone, but given a simplification of the ISO 8601 calendar date extended format such as "2014-03-07", it will assume a time zone of UTC (ES5 and ECMAScript 2015). Therefore Date objects produced using those strings may represent different moments in time depending on the version of ECMAScript supported unless the system is set with a local time zone of UTC. This means that two date strings that appear equivalent may result in two different values depending on the format of the string that is being converted.

[mathiasrw]

Nice find!

Thank you for that. I guess we will have to do the UTC dance with data.

[joearasin]

Part of the problem here is that the correct behavior is dependent on the format of the date when it's loaded. If we string format isn't enforced upon loading, the behavior of the date functions needs to be different