CODEWITHSUNDEEP
mysqlnode.jstimestamptimezonemysql-workbench
Ibrahim Farooq
Ibrahim Farooq

Reputation: 438

MySQL query in NodeJS returning current_timestamp as YYYY-MM-DDTHH:MM:SS.0000Z

I have a table named inpatient inside a MySQL schema. The table has a column admit_date which automatically generates a CURRENT_TIMESTAMP and stores it in the column when a record is created.

enter image description here

The picture shown above is of that column inside my MySQL Workbench. It is storing the correct value. Now the problem arises when I query this date inside NodeJS. If I write a simple query as:
SELECT admit_date from inpatient,
I get the following data as a result:

[
  RowDataPacket { admit_date: 2021-05-03T23:46:43.000Z },
  RowDataPacket { admit_date: 2021-05-03T23:46:43.000Z },
  RowDataPacket { admit_date: 2021-05-03T23:46:43.000Z },
  RowDataPacket { admit_date: 2021-05-03T22:53:21.000Z },
  RowDataPacket { admit_date: 2021-05-03T22:53:21.000Z },
  RowDataPacket { admit_date: 2021-05-03T22:53:21.000Z },
  RowDataPacket { admit_date: 2021-05-03T22:53:21.000Z },
  RowDataPacket { admit_date: 2021-05-03T22:53:21.000Z },
  RowDataPacket { admit_date: 2021-05-03T22:53:21.000Z },
  RowDataPacket { admit_date: 2021-05-03T22:53:21.000Z },
  RowDataPacket { admit_date: 2021-05-03T22:53:21.000Z },
  RowDataPacket { admit_date: 2021-05-06T00:57:01.000Z }
]

Is this problem NodeJs specific because it seems like so. Anyways, is there any way i could format this somewhat unreadable value or if i could write a new statement that fetches me the correct value stored in the schema? My goal is to get the timestamp exactly as it is in the admit_date column.

Upvotes: 0

Views: 970

Answers (2)

Vlad Maidow
Vlad Maidow

Reputation: 36

By default, MySQL driver for Node.js will convert Date and DateTime fields into plain Date() object, you should be able to use any available methods that it provides

https://developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Global_Objects/Date

In case if you need to format it into something more readable consider using libraries like moment, dayjs, date-fns which provide simple ways to perform conversions

You also can override default behaviour by changing typeCast parameter of database connection, for example, setting it to false will disable type casting completley

https://github.com/mysqljs/mysql#type-casting

Upvotes: 1

O. Jones
O. Jones

Reputation: 108706

In MySQL, TIMESTAMP data types are always stored in UTC time. They're stored in an internal format. When retrieved, two things happpen. They are

  • converted to the current session's time zone, and
  • converted to the 2021-04-01 12:34:556 text rendering.

If you want them converted to a particular time-zone, issue this sort of MySQL command in your connection before doing your SELECT.

SET time_zone='Asia/Kolkata';

or whatever time zone you need.

If you don't want this UTC and timezone functionality use MySQL's DATETIME data type in place of TIMESTAMP.

If you're doing hospital IT in the USA, you DO want this timezone functionality because of Medicare's notorious three-midnight rule.

Upvotes: 1

Related Questions