Setting a timezone other than UTC as default

[shadyanwar]

It seems that loopback-datasource-juggler is converting any type: Date (if defined as so in the model) to UTC before saving to the database.

By doing some digging, this is where it happens.

What makes things more confusing is that whenever the same data is fetched, it does not undergo this conversion backwards. i.e. it is displayed exactly as it is saved in the database.

Steps to reproduce

1. Create a model with a property of the type Date: @property({type: Date,...}))

2. In a machine with a timezone other than UTC (for example UTC+7), create a record with the date value: '2019-07-25T08:00'

Current Behavior

The data will be saved to the database as '2019-07-25T01:00', not taking into consideration any timezone set anywhere.

Expected Behavior

I propose that adding a timezone property in the datasource be honoured instead of defaulting to UTC. So the behaviour of converting to UTC should remain, but after that conversion a check should be made whether a different timezone is defined in the datasource and convert to it if exists: {"timezone": "UTC+07:00"}
Example:
{ "host": "127.0.0.1", "port": 3306, "database": "mydb", "username": "root", "password": "root", "name": "mydb", "connector": "mysql", "timezone": "UTC+07:00" }

My only current workaround is to change type: Date to type: String in the model property definition.

Link to reproduction sandbox

Additional information

I am reporting this as a bug since the framework's behavior is as described even if the app server (on the os level) and the database server (for example mysql, my.cnf: default-time-zone='+07:00') have a timezone set to other than UTC.

And also because this behavior is enforced even if the value of the date is defined as string. The dao overrides any possible value and performs this conversion.

Related Issues

See Reporting Issues for more tips on writing good issues

[bajtos]

@shadyanwar thank you for reporting the bug. What database are you using? (This behavior could be connector specific).

Please create a small application we can use to reproduce and debug the issue. We are quite busy and creating one ourselves would not be an effective use of our limited time. See https://loopback.io/doc/en/contrib/Reporting-issues.html#loopback-4x-bugs

[shadyanwar]

@bajtos Here you go:
https://github.com/shadyanwar/lb4-timezone-test

After running npm start, go to:
http://localhost:3000/addUser?name=test

I use moment-timezone to change the timezone and set a default one.

It will give something like the following:

{
  "createdUser": {
    "id": 1,
    "name": "test",
    "dateAdded": "2019-08-13T00:40:00.780Z"
  },
  "dateTimeNowTz": "2019-08-13T09:40:00.78+09:00"
}

The property createdUser shows the values returned from the database. Notice how the time is in UTC (00:40:00) although it was sent to the database in another timezone.
Meanwhile, the property dateTimeNowTz shows the value of the current time inside the app in Asia/Tokyo timezone (09:40:00), which is the value sent to the dao before the dao converted it to UTC.

My proposal is that dao conversion should by default still convert to UTC, but if a timezone is mentioned in the datasource file, then it should honour the mentioned timezone rather than the UTC default.

To see how the data is retrieved go to: http://localhost:3000/users
The data is retrieved as is.

[{
  "id": 1,
  "name": "test",
  "dateAdded": "2019-08-13T00:40:78.000Z"
}]

Note:
I'm using MySQL. The app is connected to a free public MySQL database which you can use right away, though it is not always reliable. Here's the MySQL table needed:

CREATE TABLE `User` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(11) DEFAULT NULL,
  `dateAdded` datetime NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8;

[dougal83]

@shadyanwar Hey! Out of curiosity, how do dates behave if you set process.env.TZ = 'Asia/Tokyo'? I think you should then be able to use features like toLocaleTimeString().

[shadyanwar]

@dougal83 Thanks for the great suggestion.

In package.json, I have added "TZ" to the "start" script as follows:
TZ=\"Asia/Tokyo\"
so now it is:
"start": "TZ=\"Asia/Tokyo\" node -r source-map-support/register .",

This has affected the conversion behavior so whenever a new Date() is stored in the database, it is stored in the database with the timezone I have set above (toLocaleTimeString() is not needed in case of storing).

For data retrieval, it is converted to UTC by default (even though it is saved in the database in my chosen timezone). However, if date.toLocaleTimeString() is used on every value, or alternatively setting a default with moment.tz.setDefault("Asia/Tokyo") at the beginning then just applying the moment to the date, like moment(date).format('YYYY-MM-DDTHH:mm:ss.SSZ'), then everything works as desired.

Anyway, I believe this is an ok situation. The only catch is that developers have to be wary of any date returned from the database before showing it in the response. They have to manually convert it to their local timezone (using either toLocaleDateString() & toLocaleTimeString(), or the moment-timezone package which works nicely). In many cases, this will mean looping through all values and reformatting them.

However, it would be ideal if the time is returned by default in the selected timezone, the timezone set at the start of the node app in the TZ environment variable.

I wonder whether it's better or more troubling if the values fetched from db (that are defined in the app as the types Date) are converted automatically to the selected timezone rather than the UTC default.

[stale[bot]]

This issue has been marked stale because it has not seen activity within six months. If you believe this to be in error, please contact one of the code owners, listed in the CODEOWNERS file at the top-level of this repository. This issue will be closed within 30 days of being stale.

[bajtos]

Sorry for not coming back to this conversation earlier. Dates, times and timezones are notoriously difficult to handle properly, double so in JavaScript.

In the past, we have had a long discussion around timezones in MySQL in the following GH issue, you may find some relevant information there too: loopbackio/loopback-connector-mysql#149 The pull request loopbackio/loopback-connector-mysql#257 should have improved the handling of time/date values by our MySQL connector.

I am afraid I don't have bandwidth to look into this issue in more details :( If you can come up with an actionable proposal (a pull request would be even better), then I am happy to provide feedback.

[bajtos]

Can we perhaps start by documenting the current behavior and best practices (like setting TZ env var)?

[thuthuatna]

I have problem too. i created a method and always pass tz in client to server, and use executeNativeSql to get the data I want. But I think it is a temporary option

[jledun]

I have a problem too : my server and client run all at GMT+2.
I have an app that is sending periodically json data including an UTC timestamp written as a correct JSON string, via POST http request.

The Counter model describes the timestamp as :

@property({
    type: 'date',
    required: true,
    mysql: {
      dataType: "DATETIME"
    }
  })
  timestamp?: string;

I use DefautCrudRepository and a datasource using loopback-connector-mysql (V6.0.2).

The JSON string describing data to insert is :

{
  "id":15729,
  "codem":"PAG_TTE",
  "timestamp":"2021-07-20T08:34:39.000Z",
  "status":1,
  "cnt1":6417,
  "cnt2":0,
  "instantRate":240,
  "averageRate":170
}

I have activated binary log on my mariadb server and I can see that the insert request generated by loopback defines a timestamp as a string without timezone information :

INSERT INTO `Counter`(`codem`,`timestamp`,`status`,`cnt1`,`cnt2`,`instantRate`,`averageRate`) VALUES('PAG_TTE','2021-07-20 08:34:39.000',1,6547,0,240,136)

Without timezone information in the insert request, MariaDB takes it as a GMT+2 timestamp and stores a wrong value :

MariaDB [iotdb]> select timestamp, UNIX_TIMESTAMP(timestamp) from Counter order by timestamp desc limit 1;
+---------------------+---------------------------+
| timestamp           | UNIX_TIMESTAMP(timestamp) |
+---------------------+---------------------------+
| 2021-07-20 08:34:39 |                1626762879 |
+---------------------+---------------------------+
1 row in set (0.004 sec)

Which gives me :

$ date --date='@1626762879';
mar. 20 juil. 2021 06:34:39 CEST

2 hours too early then all my frontend is wrong...

In package.json, I've tried to set :

"scripts": {
    ...,
    "start": "TZ=\"Europe/Paris\" node -r source-map-support/register .",

No success.

Can somebody help me to figure it out ?

Additionnal informations :

$ node -v
v12.22.0
$ tail -n 21 package.json 
  "dependencies": {
    "@loopback/boot": "^3.4.0",
    "@loopback/core": "^2.16.0",
    "@loopback/repository": "^3.6.0",
    "@loopback/rest": "^9.3.0",
    "@loopback/rest-explorer": "^3.3.0",
    "@loopback/service-proxy": "^3.2.0",
    "loopback-connector-kv-redis": "^4.0.0",
    "loopback-connector-mysql": "^6.0.0",
    "tslib": "^2.0.0"
  },
  "devDependencies": {
    "@loopback/build": "^6.4.0",
    "@loopback/eslint-config": "^10.2.0",
    "@loopback/testlab": "^3.4.0",
    "@types/node": "^15.6.2",
    "eslint": "^7.25.0",
    "source-map-support": "^0.5.19",
    "typescript": "~4.2.4"
  }
}
$ mariadb --version
mariadb  Ver 15.1 Distrib 10.5.10-MariaDB, for Linux (x86_64) using readline 5.1
$ uname -a
Linux srv-iot3 5.10.42-1-MANJARO #1 SMP PREEMPT Thu Jun 3 14:37:11 UTC 2021 x86_64 GNU/Linux

Thanks !

[jledun]

[Solved] by adding :

  "timeZone": "local",
  "legacyUtcDateProcessing": false,

into datasource settings.

[mahmoudsabaeshezlong]

Life saver. Thank you

[andrei0807]

I wanted this feature too.

[esraalaila]

I have encountered an issue where the date is being stored in my database correctly (in UTC+2) however, upon retrieval in my app, it is converted to UTC. I have solved this by adding
"dateStrings": true,
in data source settings.
dateStrings: If set to true, this forces the driver to return date and time values as strings (without offset), which can be helpful if you want to manage formatting yourself in JavaScript.