search

ropensci / writexl

Portable, light-weight data frame to xlsx exporter for R
https://docs.ropensci.org/writexl
Other
209 stars 17 forks source link

datatime column only save UTC timezone. #32

Open mrchypark opened 5 years ago

mrchypark commented 5 years ago

I change time zone in R object. but writexl function save to UTC timezone.

So, I use as.character before save but it's confuse.

Is this the intended behavior?

shrektan commented 3 years ago

I think this is because it's easier to implenment when calculating the correct the Excel numeric value of the datetime object, as R stores the datetime numeric value based on the UTC 1970-01-01 00:00:00, regardless of the timezone ... I find this inconvinient, too.

jmcnamara commented 3 years ago

Excel doesn't have support for timezones and stores everything as UTC. So unfortunately, this isn't really fixable.

I vote to close as can't/won't fix.

mrchypark commented 3 years ago

writexl::write_xlsx() function just write datetime data as string like "2021-04-17 14:16:40 UTC". https://github.com/ropensci/writexl/blob/master/src/write_xlsx.c#L89

So, My suggestion is write datetime data with timezone text like "2021-04-17 23:16:17 KST".

xlsx is not only database for machine, but also communication docs with human.

I think datetime storing as string with tz is good to R user work with others.

jmcnamara commented 3 years ago

I think datetime storing as string with tz is good to R user work with others.

That is certainly fine and that can be done by the user in the dataframe before exporting it, i.e., they can convert a datetime column to a character string column.

However, I don't think that it would be a good solution to export all datetimes to Excel as strings. This would prevent users from doing calculations based on them or graphing them.

So I think writexl should honour the conversion from R datatypes to Excel data types (where possible). Users will have to work around the Excel limitation of losing the timezone information.

mrchypark commented 3 years ago

@jmcnamara

Thanks for the great comment.

I first noticed that Excel does not support time zones.

I understand that the current writexl package does not use Excel's datetime type as long as it is processed as a string. So, I asked if we intended to treat it as a string. My feedback was provided as an improvement in the current state.