Preserving absolute date value when uploading via CSV

[pundip]

I have a CSV file with dated transactions that I want to upload via the CSV import. The problem is that the system applies some sort of timezone changing what gets stored in the database.
if the csv has 2024-02-07 then that's what I would like recorded in the database.
I am using the Jdate element set to the local time zone
- CSV Import Format = Normal
- J! time zone is set to Melbourne (my local timezone)
- Hosting server is set to Melbourne
- database server is also set to Melbourne

The date 2024-02-07 gets stored as 2024-02-06 02:00:00. I would like it to be stored as 2024-02-07 or 2024-02-07 00:00:00

What setting do I need to use to make sure the date value is stored without being offset by some timezone.

 

[wezetel]

Did you try in the settings of your date element to use local time for storing values?

 

[pundip]

Yep. I have set the element setting to local time. I have attached a screenshot.

 

[pundip]

I also want to add that when I input via web interface it saves correctly. But when i CSV upload 2024-02-07 00:00:00 it converts to 2024-02-06 13:00:00. The difference seems to be the same if I convert 2024-02-07 00:00:00 to UTC. So it looks like UTC conversion is being applied no matter what I do.
Edit: If I change the element to field the behavior persists. Looks like the conversion happens regardless.

 

[troester]

Edit: If I change the element to field the behavior persists. Looks like the conversion happens regardless.

This sounds totally strange. I can't imagine Fabrik doing conversion on a field (=text) element.

The difference seems to be the same if I convert 2024-02-07 00:00:00 to UTC.

Where, in your CSV file?

Does the CSV (looking at it with a editor like notepad++, not excel etc) contain "2024-02-07 00:00:00"?

Is there a difference if you choose "Store Date as"=UTC?

As a workaround you can add listCSV plugin (Import row PHP code) to add some hours before importing.

 

[pundip]

I cant reproduce the issue when converting the element to a field. I think this was an error on my part. When converting the element to Jdate the issue persists:

With element set to Local time

CSV value 2024-02-07 00:00:00 becomes 2024-02-06 13:00:00 in the database

With element set to UTC time

CSV value 2024-02-06 02:00:00 becomes 2024-02-06 13:00:00 in the database

I also created a separate instance and added the MySQL table as a list in that instance and it works just fine. This instance where I am experiencing the issue is an upgraded instance so maybe something went wrong in the upgrade?

 

[pundip]

Ok I think I have resolved this issue. The fix is set the website time zone to UTC and the element to UTC. This seems to work and the value CSV ends up being what gets recorded in the database.