DBF parser returning wrong date (timezone inconsistency) #663

New Issue

2017-05-20T07:21:54Z

peeyushsrivastava commented

2017-05-20 07:21:54 +00:00

(Migrated from github.com)

Hi,

When I am reading date from dbf file suppose 18/01/1974 which is in date format, J parser returning date as 17/01/1974. I am unable to understand why this is happening. Please help

Hi, When I am reading date from dbf file suppose 18/01/1974 which is in date format, J parser returning date as 17/01/1974. I am unable to understand why this is happening. Please help

SheetJSDev commented

2017-05-20 14:27:24 +00:00

(Migrated from github.com)

Can you share an offending file?

e-cloud commented

2017-05-25 15:19:44 +00:00

(Migrated from github.com)

I can confirm it. When running the test suite on CI platform, there is no error. But when running it on my computor, it fails the following tests. And i'm in China. Its timezone varies from USA.

@SheetJSDev you may change the timezone of your computor to get those error.

I can confirm it. When running the test suite on CI platform, there is no error. But when running it on my computor, it fails the following tests. And i'm in China. Its timezone varies from USA. ![image](https://cloud.githubusercontent.com/assets/5255354/26456671/5c9f7726-41a0-11e7-9c7c-9bca179f71f5.png) @SheetJSDev you may change the timezone of your computor to get those error.

SheetJSDev commented

2017-05-25 15:58:30 +00:00

(Migrated from github.com)

@e-cloud we had an email back and forth wherein we discovered the issue was a timezone inconsistency.

What is unknown is the intended timezone for these files. Landscape looks like this:

BIFF8 XLS technically has a "Country" record which specifies the country, which is sufficient for single-time-zone countries like China (which uses CST) but not for multi-time-zone countries like US (which has 4 timezones).
ODS and friends support a date/time cell type where the dates and times are UTC
XLSX, XLSB, older XLS, and SYLK use numeric date codes, where the time zone is not specified anywhere in the file
XLSX additionally has a date type, whose value is an ISO8601 date or duration depending on value type (generally UTC)
SpreadsheetML has an ISO8601 date type (generally UTC)
None of the lotus or quattro pro types have a timezone specifier
The older formats, including CSV and DBF, have no timezone specifier

There are two questions to be answered:

How do you specify the timezone? That probably should be an explicit option to the various read functions.
When is a specified date/time based in UTC and when is a specified date/time for the current time zone? Excel generally uses the current time zone, which would suggest we should treat CSV datetimes as based in the timezone of the person who saved the file. We haven't played with dBASE/FoxPro (to determine what we should do with DBF).

@e-cloud we had an email back and forth wherein we discovered the issue was a timezone inconsistency. What is unknown is the intended timezone for these files. Landscape looks like this: - BIFF8 XLS technically has a "Country" record which specifies the country, which is sufficient for single-time-zone countries like China (which uses CST) but not for multi-time-zone countries like US (which has 4 timezones). - ODS and friends support a date/time cell type where the dates and times are UTC - XLSX, XLSB, older XLS, and SYLK use numeric date codes, where the time zone is not specified anywhere in the file - XLSX additionally has a date type, whose value is an ISO8601 date or duration depending on value type (generally UTC) - SpreadsheetML has an ISO8601 date type (generally UTC) - None of the lotus or quattro pro types have a timezone specifier - The older formats, including CSV and DBF, have no timezone specifier There are two questions to be answered: 1) How do you specify the timezone? That probably should be an explicit option to the various read functions. 2) When is a specified date/time based in UTC and when is a specified date/time for the current time zone? Excel generally uses the current time zone, which would suggest we should treat CSV datetimes as based in the timezone of the person who saved the file. We haven't played with dBASE/FoxPro (to determine what we should do with DBF).

👍 1

e-cloud commented

2017-05-27 10:44:17 +00:00

(Migrated from github.com)

@SheetJSDev are you stating out the problems or asking me?

if it's the the later one, in fact, i don't even use the published code to get the error. I just run the test suite inside js-xlsx and the errors occur. So I don't specify any timezone. if it has any timezone specified, it is specified in the source code of js-xlsx.

If it's the prior, sorry that i don't have enough knowledge about time processing.

@SheetJSDev are you stating out the problems or asking me? if it's the the later one, in fact, i don't even use the published code to get the error. I just run the test suite inside js-xlsx and the errors occur. So I don't specify any timezone. if it has any timezone specified, it is specified in the source code of js-xlsx. If it's the prior, sorry that i don't have enough knowledge about time processing.

SheetJSDev commented

2017-05-30 21:48:26 +00:00

(Migrated from github.com)

@e-cloud @peeyushsrivastava we went all the way back and tested many versions of Excel. The full answer is: Even if files have a proper ISO8601 time specification with timezone, Excel disregards the timezone and interprets in local time (e.g. an XLSX file with date cell set to 2017-05-30T18:06:24.403Z will appear as 2017-05-30 18:06:24 on any computer). Date codes appear the same in every time zone. There is no concept of absolute time.

So to replicate the Excel behavior and still maintain some semblance of JS sanity, we will add an optional timezone offset parameter to the input and output functions. The "default" will be the local timezone as determined by new Date().getTimezoneOffset(). We will also need to add a field to the workbook object to store the original timezone offset for use cases like reading a file in a browser in China and saving on a server in New York.

@e-cloud there is very little documentation covering Excel's behavior. The aforementioned explanation is mostly a summary of my notes from testing, as that will hopefully help future people to understand the situation.

@e-cloud @peeyushsrivastava we went all the way back and tested many versions of Excel. The full answer is: Even if files have a proper ISO8601 time specification with timezone, Excel disregards the timezone and interprets in local time (e.g. an XLSX file with date cell set to `2017-05-30T18:06:24.403Z` will appear as `2017-05-30 18:06:24` on any computer). Date codes appear the same in every time zone. There is no concept of absolute time. So to replicate the Excel behavior and still maintain some semblance of JS sanity, we will add an optional timezone offset parameter to the input and output functions. The "default" will be the local timezone as determined by `new Date().getTimezoneOffset()`. We will also need to add a field to the workbook object to store the original timezone offset for use cases like reading a file in a browser in China and saving on a server in New York. @e-cloud there is very little documentation covering Excel's behavior. The aforementioned explanation is mostly a summary of my notes from testing, as that will hopefully help future people to understand the situation.

👍 1

SheetJSDev commented

2017-06-01 21:31:03 +00:00

(Migrated from github.com)

We updated the travis config so it should test against different timezones: https://github.com/SheetJS/js-xlsx/blob/master/.travis.yml#L13-L28 . We also updated our internal scripts so we should pick up on international concerns in the future.

We included some timezones with positive and some timezones with negative offsets from UTC. We also included some cases with DST and other cases with no DST.

We updated the travis config so it should test against different timezones: https://github.com/SheetJS/js-xlsx/blob/master/.travis.yml#L13-L28 . We also updated our internal scripts so we should pick up on international concerns in the future. We included some timezones with positive and some timezones with negative offsets from UTC. We also included some cases with DST and other cases with no DST.

👍 1

peeyushsrivastava commented

2017-06-20 13:08:29 +00:00

(Migrated from github.com)

THanks a lot for the quick fix, it's working good for me now.

On Fri, Jun 2, 2017 at 3:01 AM, SheetJSDev notifications@github.com wrote:

We updated the travis config so it should test against different
timezones: https://github.com/SheetJS/js-xlsx/blob/master/.travis.yml#
L13-L28 . We also updated our internal scripts so we should pick up on
international concerns in the future.

We included some timezones with positive and some timezones with negative
offsets from UTC. We also included some cases with DST and other cases with
no DST.

—
You are receiving this because you were mentioned.
Reply to this email directly, view it on GitHub
https://github.com/SheetJS/js-xlsx/issues/663#issuecomment-305626638,
or mute the thread
https://github.com/notifications/unsubscribe-auth/AC_vP1SxsswJ6SJM20CBqbHSJ9bxrz5Yks5r_y2dgaJpZM4NhN_7
.

--
Thanks and Regards,

Peeyush Srivastava

THanks a lot for the quick fix, it's working good for me now. On Fri, Jun 2, 2017 at 3:01 AM, SheetJSDev <notifications@github.com> wrote: > We updated the travis config so it should test against different > timezones: https://github.com/SheetJS/js-xlsx/blob/master/.travis.yml# > L13-L28 . We also updated our internal scripts so we should pick up on > international concerns in the future. > > We included some timezones with positive and some timezones with negative > offsets from UTC. We also included some cases with DST and other cases with > no DST. > > — > You are receiving this because you were mentioned. > Reply to this email directly, view it on GitHub > <https://github.com/SheetJS/js-xlsx/issues/663#issuecomment-305626638>, > or mute the thread > <https://github.com/notifications/unsubscribe-auth/AC_vP1SxsswJ6SJM20CBqbHSJ9bxrz5Yks5r_y2dgaJpZM4NhN_7> > . > -- Thanks and Regards, Peeyush Srivastava

markneisler commented

2017-10-05 20:18:05 +00:00

(Migrated from github.com)

this was what I had to do to solve my similar problem maybe it or a piece of it can help you. Updating lib did not solve my problem.

this was what I had to do to solve my similar problem maybe it or a piece of it can help you. Updating lib did not solve my problem. var wb = XLSX.utils.table_to_book(document.getElementById(id), {sheet: "Sheet JS"}); var sheet = wb.Sheets["Sheet JS"]; for (var address in sheet) { var cell = sheet[address]; if (cell) { if (cell.z) { cell.v = cell.v + (new Date().getTimezoneOffset() / 1440); } } }

👍 2

mplattu commented

2018-07-25 07:49:01 +00:00

(Migrated from github.com)

@markneisler Thanks for this as updating the library did not magically help me either (I'm running js-xlsx in browser). According to MSDN documentation getTimezoneOffset() return difference between current locale and UTC. In my GMT+2 (+3 during daylight savings time) the browser console says:

var d=new Date();
console.log(d.getTimezoneOffset());
-180

Therefore, the time zone adjustment should be subtracted, not added:

cell.v = cell.v - (new Date().getTimezoneOffset() / 1440);

@markneisler Thanks for this as updating the library did not magically help me either (I'm running js-xlsx in browser). According to [MSDN documentation](https://developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Global_Objects/Date/getTimezoneOffset) `getTimezoneOffset()` return difference between current locale and UTC. In my GMT+2 (+3 during daylight savings time) the browser console says: ``` var d=new Date(); console.log(d.getTimezoneOffset()); -180 ``` Therefore, the time zone adjustment should be subtracted, not added: `cell.v = cell.v - (new Date().getTimezoneOffset() / 1440);`

Sign in to join this conversation.