alasql＆amp;按子句订购日期

Question

我需要按日期和事件订购我的数据，但是我正在努力使用日期对象的Alasql查询来使其工作：

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);
}

并且获得的结果是：

[ { 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) } ]

我期望：

[ { 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) } ]

如果日期不是日期是日期，而是ISO，则不会发生问题字符串：

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);
}

结果与预期的d，a，b，c

有什么想法吗？

Answer 1

有必要创建表模式以正确地将列视为日期类型，如下所示：

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>