Typeorm: find operators like MoreThan and LessThan doesn't work properly for date fields

[mkeremguc]

Issue type:

[ ] question
[x] bug report
[ ] feature request
[ ] documentation issue

Database system/driver:

[ ] cordova
[ ] mongodb
[ ] mssql
[ ] mysql / mariadb
[ ] oracle
[ ] postgres
[x] sqlite
[ ] sqljs
[ ] react-native

TypeORM version:

[x] latest
[ ] @next
[ ] 0.x.x (or put your version here)

Steps to reproduce or a small repository showing the problem:
find operators like MoreThan and LessThan doesn't work properly for date fields. some investigation showed that sqlite stores dates like 2018-06-05 15:57:27.249 but the query is like ... WHERE "User"."createdAt" > ? -- PARAMETERS: ["2018-06-05T01:01:32.650Z"] meaning it queries in ISO date format. when you just need comparison till day factor, it doesnt look like a problem but when you also need the time/hours/mins/etc then it doesnt return proper results. i only tested for sqlite so i dont know if same problem goes for other dbs.

[enif-lee]

Hello guys. I'm still working this issue. I don't know sqlite as well but I did test few cases about comparing datetime values.

What's the problem.

-- 1. getTime() doesn't working, EXPECTED: FALSE, ACTUAL: TRUE
select DATETIME('2019-01-10T00:00:00Z') > 9999999999999999999999999999999999999999999999999999999999999999999999999;

-- 2. toUTCString(), doesn't working, EXPECTED: TRUE, ACTUAL: FALSE
select DATETIME('2019-01-10T00:00:00Z') > 'Mon, 31 Dec 2018 15:00:00 GMT';

-- 3. toISOString()
-- Seem to work
-- EXPECTED: TRUE, ACTUAL: TRUE
select DATETIME('2019-01-10T00:00:00Z') > '2019-01-01T00:00:00.000Z';

-- What? Is this about timezone issue?
-- EXPECTED: TRUE, ACTUAL: FALSE
select DATETIME('2019-01-10T08:00:00Z') > '2019-01-10T00:00:00.000Z';

-- SQLite is configured UTC timezone as default on any environment,
-- EXPECTED: 2019-01-01 00:00:00, ACTUAL: 2019-01-01 00:00:00
select DATETIME('2019-01-01T00:00:00Z');

-- DATETIME comparing is working
-- EXPECTED: true, ACTUAL: true
select DATETIME('2019-01-01T00:00:00Z') = DATETIME('2019-01-01T00:00:00Z');
select DATETIME('2019-01-01T01:00:00Z') > DATETIME('2019-01-01T00:00:00Z');

-- 4. Finally I found the root cause.
-- THAT IS JUST STRING COMPARING.
select DATETIME('2019-01-01T12:00:00Z'), -- RESULT: 2019-01-01 12:00:00
       '2019-01-01T00:00:00Z', -- RESULT: 2019-01-01T00:00:00Z
       DATETIME('2019-01-01T12:00:00Z') > '2019-01-01T00:00:00Z', -- false
       DATETIME('2019-01-01T12:00:00Z') > '2019-01-01 00:00:00'   -- true

YES, sqlite support ISO 8601 date format, but it's just about insert or update, not about value comparing.
and sqlite doesn't have type to storing date, datetime, timestamp and that will be stored as text type.. In my think, datetype is just alias for text

-- for date
create table user
(
  id        integer  not null
    primary key autoincrement,
  createdAt datetime not null
);
select typeof(createdAt)
from user;

What's the solution.

1. In now, as the best simple solution. format the datetime value.

export const MoreThanDate = (date: Date) => MoreThan(format(new Date(), 'yyyy-mm-dd HH:MM:ss.l'))
// ...
const user = await repository.findOne({
    createdAt: MoreThanDate(new Date())
})

1. Contribute support datetime in find interface.

[bioleyl]

@enif-lee
Hello, I would like to try your MoreThanDate solution but I have a question.

From where come that "format()" function in the MoreThan?

Regards

[enif-lee]

@bioleyl Uhmm, this is from just simple external node package.

Try to check below links. :)

1. https://www.npmjs.com/package/dateformat
2. https://www.npmjs.com/package/date-format

[bioleyl]

@enif-lee
Ok thanks. I usually use an other one:
https://www.npmjs.com/package/date-fns
You just don't use the parameter date and you force new Date() in your function. Here is an update if someone is interested:

import { LessThan, MoreThan } from 'typeorm'
import { format } from 'date-fns'

// TypeORM Query Operators
export const MoreThanDate = (date: Date) => MoreThan(format(date, 'YYYY-MM-DD HH:MM:SS'))
export const LessThanDate = (date: Date) => LessThan(format(date, 'YYYY-MM-DD HH:MM:SS'))
// ...
const user = await repository.findOne({
    createdAt: MoreThanDate(new Date())
})

[pleerock]

Is it a sqlite-specific issue?

[jankarres]

Thank you for your solutions, @enif-lee and @bioleyl. I have added the functions MoreThanOrEqualDate and LessThanOrEqualDate to them. Also, your solution worked with datetime, but not with date (relevant in the case of equl), which is why I differentiated that. In the following several variants, depending on whether you use [email protected] or [email protected]. Tested with MySQL/MariaDB.

[email protected]

import { MoreThan, MoreThanOrEqual, LessThan, LessThanOrEqual } from "typeorm";
import { format } from "date-fns";

// TypeORM query operators polyfills
enum EDateType {
  Date = "yyyy-MM-dd",
  Datetime = "yyyy-MM-dd HH:MM:ss"
};

const MoreThanDate = (date: Date, type: EDateType) => MoreThan(format(date, type));
const MoreThanOrEqualDate = (date: Date, type: EDateType) => MoreThanOrEqual(format(date, type));
const LessThanDate = (date: Date, type: EDateType) => LessThan(format(date, type));
const LessThanOrEqualDate = (date: Date, type: EDateType) => LessThanOrEqual(format(date, type));

export { MoreThanDate, MoreThanOrEqualDate, LessThanDate, LessThanOrEqualDate, EDateType };

[email protected]

import { MoreThan, MoreThanOrEqual, LessThan, LessThanOrEqual } from "typeorm";
import { format } from "date-fns";

// TypeORM query operators polyfills
enum EDateType {
  Date = "YYYY-MM-DD",
  Datetime = "YYYY-MM-DD HH:MM:SS"
};

const MoreThanDate = (date: Date, type: EDateType) => MoreThan(format(date, type));
const MoreThanOrEqualDate = (date: Date, type: EDateType) => MoreThanOrEqual(format(date, type));
const LessThanDate = (date: Date, type: EDateType) => LessThan(format(date, type));
const LessThanOrEqualDate = (date: Date, type: EDateType) => LessThanOrEqual(format(date, type));

export { MoreThanDate, MoreThanOrEqualDate, LessThanDate, LessThanOrEqualDate, EDateType };

[ospfranco]

I was doing unit testing and found this problem, how is this still open for so long... very basic functionallity for an ORM.

I have a postgres db with timestamp fields in my entity, this is what I ended up using to make it work:

import { format } from 'date-fns';

export function ISOToDBDate(isoDate: string) {
  return format(new Date(isoDate), 'yyyy-MM-dd kk:mm:ss.SSS');
}

[dgurns]

Thank you all for the info. This drove me crazy for a while and finally I solved it like this:

My dates are saved in SQLite as ISO strings in UTC like "2020-05-26T10:21:41.958Z"

To compare saved dates against the current time, I did:

const currentUtcTimeAsSqliteString = new Date().toISOString().replace('T', ' ');

const shows = await getManager()
      .createQueryBuilder(Show, 'show')
      .where(
        'show.showtimeInUtc > :now',
        {
          now: currentUtcTimeAsSqliteString,
        }
      )
      .orderBy('show.showtimeInUtc', 'ASC')
      .getMany();

[ozknemoy]

for oracle this is enough: MoreThan(new Date('2020-05-31T21:00:00.000Z'))