Date column (fields.fieldType == 10) returns the previous day in MySQL 5.7

[mauroc8]

Basically, I have a query that filters using date AND date BETWEEN ? AND ? and I get dates out of that range (specifically, the first row was one day earlier to the starting range).

That's when I started comparing the results with the same query executed in DBeaver and I realized that the Deno connector is consistently returning the previous day to the one saved in the database. This only happened in fields whose fieldType was 10 (date only). I have other date types (11: time only, 12: date and time) that work well.

I made a quick fix by switching over the fieldType and adding one to the date I received (luckily, I was switching over the type anyway because I was changing the way I stringify the date based on it).

I thought it would be nice to report the issue. Sadly I don't have a minimal codebase where I can reproduce the error. I aknowledge it could be a problem in the database configuration. But I am not having the same problem using MySQL Workbench or DBeaver.

MySQL version: 5.7.31
deno_mysql version: v2.10.0

[mauroc8]

Update: I found out my PC is on UTC -03 and the database is on UTC. It's probably some timezone issue. I will be investigating further this issue. I really want to keep using deno for this :)

[mauroc8]

Ok, so I finally have time to look into this.

I'm finding out that the Date object has the correct date:

console.log({ date, string: dateToString(date) })
// { date: 2021-08-31T00:00:00.000Z, string: "08-30-2021" }

dateToString uses Date.prototype.getDate, getMonth() + 1, getFullYear(), etc, but I should've been using Date.prototype.getUTCDate, etc.

But now, why do I need to use the UTC variants only in that column (type 10) and not in the other ones (type 11 and 12)?

ACTUALLY, I tried with the UTC versions in the other columns and it worked fine. It seems like in the columns of type 11 and 12 I get the same result using getUTCDate and getDate, but in the column of type 10 I only get the correct result using getUTCDate (same is true for getUTCMonth, etc). (This is false, I didn't test it correctly).

I think I should close this issue because it seems like it's a misunderstanding from my part on how to work with Dates in deno.

[mauroc8]

This problem is related to the Date API reading dates in UTC instead of local date-time.

Consider these (my local timezone is UTC -3):

new Date('2021-01-01 00:00:00')
> Date Fri Jan 01 2021 00:00:00 GMT-0300 (hora estándar de Argentina)
new Date('2021-01-01')
> Date Thu Dec 31 2020 21:00:00 GMT-0300 (hora estándar de Argentina)

I think the solution is appending 00:00:00 to the date. Eg in result.ts:

function convertType(field: FieldInfo, val: string): any {
  // ...
  switch (fieldType) {
    // ...
    case MYSQL_TYPE_DATE:
      return new Date(`${val} 00:00:00`);
    case MYSQL_TYPE_NEWDATE:
    case MYSQL_TYPE_TIMESTAMP:
    case MYSQL_TYPE_DATETIME:
    case MYSQL_TYPE_TIMESTAMP2:
    case MYSQL_TYPE_DATETIME2:
      return new Date(val);
    default:
      return val;
  }

I can't test it today because I don't have docker and I don't want to clutter this computer. I'll test it with my other computer other time.

(By the way, it helps a lot that the sql_builder's formatDate uses getFullYear, getMonth, getDate, etc instead of toISOString.)

[TehShrike]

Passing around a MySQL DATE type in a JavaScript Date data structure practically guarantees that you'll run into bugs.

Dates should be passed around as strings until the Temporal proposal lands and we can pass them around as PlainDates.

const pad2 = (value: number | string) => value.toString().padStart(2, '0')

const utc_to_iso_date = (date: Date) =>
	`${date.getUTCFullYear()}-${pad2(date.getUTCMonth() + 1)}-${pad2(date.getUTCDate())}`

[suchislife801]

It infuriates me too, that every single God forsaken mysql library for Deno/Node feels like Dates is something they should take care of for you. Like MySQL doesn't accept Dates as string since the beginning of time.

Take a look at this one:
How to use MySQL2 with Deno

And their official GitHub page: mysql2