Date field issues

[Stibs]

System Information

• Strapi Version: [v3.2.5]
• Operating System: Windows10 10.0.18363
• Database: sqlite
• Node Version: 14.15.0
• NPM Version: 6.14.8
• Yarn Version: NA

Hi guys. Anytime I try to submit a ISO formatted date it comes out of graphql two days behind. Can anyone tell me why this is occurring?

Data from Sqlite Db:
|id|date|time|created_by|updated_by|created_at|updated_at|group|
|22|2021-02-19|afternoon|||1612309003097|1612309003097||

PerferredDateTime is a simple model with only a Date (not DateTime or Time) field and an enum (called ‘Time’).

I have attempted to submit a full ISO string and it was rejected. I checked that my browser and OS are on the correct timezone. I haven’t changed any settings dealing with time for Strapi or Sqlite.

Any ideas or suggestions? Thanks

[Stibs]

Tried a clean install of 3.4.6 and the below still occurs.

I created a preferred-date-time object using REST and postman. Yet the response still comes back with a date field a day behind, which makes me think the JSON is being parsed into a dateTime object, without the timezone, and then returned a day behind because the timezone is 0. Still not sure how to fix this.

Here is my REST create request and response with the database data below.

So how am I supposed to use a Date field when any date I put in uses a timezone that I can’t enter?

[sunnyson]

The database uses timezone also, the default value is utc.
You can change it in ./config/database.js:

module.exports = ({ env }) => ({
  defaultConnection: 'default',
  connections: {
    default: {
      connector: 'bookshelf',
      settings: {
        client: 'sqlite',
        timezone: 'utc' //CHANGE IT HERE TO SOMETHING ELSE
        filename: env('DATABASE_FILENAME', '.tmp/data.db'),
      },
      options: {
        useNullAsDefault: true,
      },
    },
  },
});

List of available timezones.

[Stibs]

Thanks for the reply Sunnyson. So I changed the timezone setting to: “utc-7”, “utc”, “America/Denver”, and just removed it.

module.exports = ({ env }) => ({
    defaultConnection: "default",
    connections: {
        default: {
            connector: "bookshelf",
            settings: {
                client: "sqlite",
                filename: env("DATABASE_FILENAME", ".tmp/data.db"),
                timezone: "America/Denver",
            },
            options: {
                useNullAsDefault: true,
            },
        },
    },
});

I also deleted the database each time. There was no change to the admin panel requests or responses, or REST responses.

I created a preferred-date-time object in the admin panel for each timezone setting. On each this data came from the create request:

{“date”:“2021-03-10T19:00:00.000Z”,“time”:“afternoon”}

I would expect this to be T17:00:00.000Z (or is it T-07:00:00.000Z). I live in utc-7 and my browser is set for utc-7.

The response from the api to the admin panel is always a day behind:

{“id”:1,“date”:“2021-03-09”,“time”:“afternoon”,“group”:null,“created_by”:…},“created_at”:“2021-02-16T15:26:13.585Z”,“updated_at”:“2021-02-16T15:26:13.593Z”}

Nothing changes for each timezone setting for REST requests either. What’s in the database is always correct though: 2021-03-10.

I do know that sqlite dbs default to utc. From what I’ve read Strapi also defaults to utc. Yet it sure seems like Strapi is adjusting dates.

Is there some configuration I’m missing and why is the timezone setting not changing anything?

[Stibs]

I did find this with REST

Request:
POST: http://localhost:1337/preferred-date-times
{
    "date": "2021-05-10T05:00:00.000Z",   <== 05:00:00.000Z 
    "time": "afternoon"
}
Reponse:
{
    "date": "2021-05-08",  <== Two days behind
    ...
}

In Db: 2021-05-09



Request:
{
    "date": "2021-05-10T06:00:00.000Z",   <== 06:00:00.000Z
    "time": "afternoon"
}
Response:
{
    "date": "2021-05-09",  <== One day behind
    ...
}

In Db: 2021-05-10

So somehow 30 hours is being subtracted from any Date Field I send to the Strapi API. Also it turns out for date fields I can submit full date times not just dates. This does not work in GraphQL.

This only occurs with Date fields

Request:
{
    "mydate":     "2021-05-10T06:00:00.000Z",    <==Identical date times
    "mydatetime": "2021-05-10T06:00:00.000Z"
}

Response:
{
    ...
    "mydate":     "2021-05-09",                     <== One day behind
    "mydatetime": "2021-05-10T06:00:00.000Z",       <== Correct
}

I tested the above with a clean install of 3.4.6. I don’t know why this is happening shrug

[Nicolas400]

Hello,
can you solve this issue ?
Setting timezone indatabase.js make no difference…

atdbconnection: {

      connector: 'bookshelf',

      settings: {

        client: 'mysql',

        host: env('DATABASE_HOST', '127.0.0.1'),

        port: env.int('DATABASE_PORT', 3306),

        database: env('ATDATABASE_NAME', 'agrologic'),

        username: env('ATDATABASE_USERNAME', 'root'),

        password: env('ATDATABASE_PASSWORD', ''),

        ssl: env.bool('DATABASE_SSL', false),

        timezone: 'utc -3',

      },

      options: {

        autoMigration: false,

      }

    },