I need to get my data ordered by date and events but I'm struggling to get it working using AlaSQL query on Date objects:
function testOrderBy() {
var data = [{event:'A', date: new Date('2021-04-21')},
{event:'B', date: new Date('2021-04-21')},
{event:'C', date: new Date('2021-04-21')},
{event:'D', date: new Date('2021-04-20')}];
console.log(data);
var res = alasql(`SELECT event, date FROM ? ORDER BY date, event`, [data]);
console.log(res);
}
And the result obtained is:
[ { event: 'D',
date: Tue Apr 20 2021 02:00:00 GMT+0200 (Central European Summer Time) },
{ event: 'C',
date: Wed Apr 21 2021 02:00:00 GMT+0200 (Central European Summer Time) },
{ event: 'B',
date: Wed Apr 21 2021 02:00:00 GMT+0200 (Central European Summer Time) },
{ event: 'A',
date: Wed Apr 21 2021 02:00:00 GMT+0200 (Central European Summer Time) } ]
I was expecting:
[ { event: 'D',
date: Tue Apr 20 2021 02:00:00 GMT+0200 (Central European Summer Time) },
{ event: 'A',
date: Wed Apr 21 2021 02:00:00 GMT+0200 (Central European Summer Time) },
{ event: 'B',
date: Wed Apr 21 2021 02:00:00 GMT+0200 (Central European Summer Time) },
{ event: 'C',
date: Wed Apr 21 2021 02:00:00 GMT+0200 (Central European Summer Time) } ]
The problem does not occur if dates are not Date objects but ISO strings:
function testOrderBy() {
var data = [{event:'A', date: '2021-04-21'},
{event:'B', date: '2021-04-21'},
{event:'C', date: '2021-04-21'},
{event:'D', date: '2021-04-20'}];
console.log(data);
var res = alasql(`SELECT event, date FROM ? ORDER BY date, event`, [data]);
console.log(res);
}
The result is as expected D, A, B, C
Any idea ?
It is necessary to create the table schema to correctly consider the column as a Date type, as follows:
alasql("CREATE TABLE events (event string, date date)");
alasql.tables.events.data = [{event:'A', date: new Date('2021-04-21')},
{event:'B', date: new Date('2021-04-21')},
{event:'C', date: new Date('2021-04-21')},
{event:'D', date: new Date('2021-04-20')}];
alasql(`SELECT event, date INTO HTML("#res") FROM events ORDER BY date, event`);<script src="https://cdn.jsdelivr.net/npm/alasql@1.7.3/dist/alasql.min.js"></script>
<div id="res">
</div>