AlaSQL & ORDER BY clause with dates

Question

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 ?

Answer 1

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/[email protected]/dist/alasql.min.js"></script>
<div id="res">

</div>