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 ?

The "SQL standard" says that if two values are considered equal in order by then their relative order is implementation dependent. `D, A, B, C` and `D, B, C, A` are both correct. — Salman A, Apr 22 '22 at 06:26
Ok but it looks like an AlaSQL issue. Ordering is D, A, B, C using dates in string format but D, B, C, A using Date objects — Fabrice, Apr 22 '22 at 06:35
Both orders are correct. The point is that there is no guarantee according to SQL standard. — Salman A, Apr 22 '22 at 06:45

score 0 · Answer 1 · answered May 17 '22 at 12:59

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>

AlaSQL & ORDER BY clause with dates

1 Answers1