New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Feature request: Add docs on timezone aware/naive datetime #257

Closed
jmcnamara opened this Issue May 20, 2015 · 10 comments

Comments

3 participants
@jmcnamara
Owner

jmcnamara commented May 20, 2015

Excel, and thus XlsxWriter, doesn't support timezones in dates/times.

The current recommendation is to remove or adjust the timezone from the datetime before passing it to XlsxWriter.

Something like this from the pytz docs:

dt = datetime(2005, 3, 1, 14, 13, 21, tzinfo=utc)
naive = dt.replace(tzinfo=None)

This should be documented in the Working with Dates and Times section of the docs.

Possibly, a constructor option could be added to remove any timezones in the data.

@scharfmn

This comment has been minimized.

Show comment
Hide comment
@scharfmn

scharfmn May 27, 2015

Workaround (if you know you have either aware anything, or naive utc):

in utility.py add:

import pytz
utc = pytz.timezone('UTC')

then change the datetime_to_excel_datetime function so that it begins as follows (via http://stackoverflow.com/a/27596917/1599229):

def datetime_to_excel_datetime(dt_obj, date_1904):
    # Convert a datetime object to an Excel serial date and time. The integer
    # part of the number stores the number of days since the epoch and the
    # fractional part stores the percentage of the day.

    try:
        dt_obj = dt_obj.astimezone(pytz.utc) # aware object can be in any timezone
    except ValueError: # naive
        dt_obj = dt_obj.replace(tzinfo=pytz.utc) # d must be in UTC


    if date_1904:
        # Excel for Mac date epoch.
        epoch = datetime.datetime(1904, 1, 1).replace(tzinfo=pytz.utc)
    else:
        # Default Excel epoch.
        epoch = datetime.datetime(1899, 12, 31).replace(tzinfo=pytz.utc)

Workaround (if you know you have either aware anything, or naive utc):

in utility.py add:

import pytz
utc = pytz.timezone('UTC')

then change the datetime_to_excel_datetime function so that it begins as follows (via http://stackoverflow.com/a/27596917/1599229):

def datetime_to_excel_datetime(dt_obj, date_1904):
    # Convert a datetime object to an Excel serial date and time. The integer
    # part of the number stores the number of days since the epoch and the
    # fractional part stores the percentage of the day.

    try:
        dt_obj = dt_obj.astimezone(pytz.utc) # aware object can be in any timezone
    except ValueError: # naive
        dt_obj = dt_obj.replace(tzinfo=pytz.utc) # d must be in UTC


    if date_1904:
        # Excel for Mac date epoch.
        epoch = datetime.datetime(1904, 1, 1).replace(tzinfo=pytz.utc)
    else:
        # Default Excel epoch.
        epoch = datetime.datetime(1899, 12, 31).replace(tzinfo=pytz.utc)
@jmcnamara

This comment has been minimized.

Show comment
Hide comment
@jmcnamara

jmcnamara May 27, 2015

Owner

Hi @scharfmn,

Since Excel doesn't support timezones I'm not in favour of putting a workaround into the module apart from removing the timezone completely (as a constructor option).

I think that anything that introduces silent handling of timezones would cause more problems than it fixed.

John

Owner

jmcnamara commented May 27, 2015

Hi @scharfmn,

Since Excel doesn't support timezones I'm not in favour of putting a workaround into the module apart from removing the timezone completely (as a constructor option).

I think that anything that introduces silent handling of timezones would cause more problems than it fixed.

John

@jmcnamara

This comment has been minimized.

Show comment
Hide comment
@jmcnamara

jmcnamara May 27, 2015

Owner

Sorry for the double confusion caused. I initially thought this was a comment added to another, older, PR.

John

Owner

jmcnamara commented May 27, 2015

Sorry for the double confusion caused. I initially thought this was a comment added to another, older, PR.

John

@scharfmn

This comment has been minimized.

Show comment
Hide comment
@scharfmn

scharfmn May 27, 2015

np - and thank you for a really clear and useful library !

np - and thank you for a really clear and useful library !

@jmcnamara jmcnamara added short term and removed medium term labels Sep 30, 2015

@akrherz

This comment has been minimized.

Show comment
Hide comment
@akrherz

akrherz Sep 22, 2016

Any further commentary on this issue? I seem to be hitting it hard now with either an update to psycopg2 or pandas causing all my datetime objects to now have tzinfo set :) Thanks for the suggested workarounds and I appreciate this library greatly!

akrherz commented Sep 22, 2016

Any further commentary on this issue? I seem to be hitting it hard now with either an update to psycopg2 or pandas causing all my datetime objects to now have tzinfo set :) Thanks for the suggested workarounds and I appreciate this library greatly!

@akrherz

This comment has been minimized.

Show comment
Hide comment
@akrherz

akrherz Sep 22, 2016

For what it is worth, my approach was just this:

def datetime_to_excel_datetime(dt_obj, date_1904):
    # Convert a datetime object to an Excel serial date and time. The integer
    # part of the number stores the number of days since the epoch and the
    # fractional part stores the percentage of the day.

    # if dt_obj has time zone information, remove it
    if dt_obj.tzinfo:
        dt_obj = dt_obj.replace(tzinfo=None)

akrherz commented Sep 22, 2016

For what it is worth, my approach was just this:

def datetime_to_excel_datetime(dt_obj, date_1904):
    # Convert a datetime object to an Excel serial date and time. The integer
    # part of the number stores the number of days since the epoch and the
    # fractional part stores the percentage of the day.

    # if dt_obj has time zone information, remove it
    if dt_obj.tzinfo:
        dt_obj = dt_obj.replace(tzinfo=None)
@jmcnamara

This comment has been minimized.

Show comment
Hide comment
@jmcnamara

jmcnamara Sep 23, 2016

Owner

If this is causing an issue with Pandas then I'll add the constructor workaround that I mentioned above.

Owner

jmcnamara commented Sep 23, 2016

If this is causing an issue with Pandas then I'll add the constructor workaround that I mentioned above.

@akrherz

This comment has been minimized.

Show comment
Hide comment
@akrherz

akrherz Sep 23, 2016

@jmcnamara thanks. For what it is worth, my comments on this... I agree with what you are saying, but I also don't think it is bad for this 'silent behavior' to be added. If a user is exporting to excel, they should know that their chosen export format does not support time zones. So within the Excel file, they would expect to 'see' the same timestamp as what their current datetime object presents within whatever timezone chosen. I worry that if the objects are arbitrarily converted to UTC and then "printed" into Excel as UTC, this would give an equally undesired outcome?

Maybe just emit a warning when this is encountered and not be totally 'silent'?

akrherz commented Sep 23, 2016

@jmcnamara thanks. For what it is worth, my comments on this... I agree with what you are saying, but I also don't think it is bad for this 'silent behavior' to be added. If a user is exporting to excel, they should know that their chosen export format does not support time zones. So within the Excel file, they would expect to 'see' the same timestamp as what their current datetime object presents within whatever timezone chosen. I worry that if the objects are arbitrarily converted to UTC and then "printed" into Excel as UTC, this would give an equally undesired outcome?

Maybe just emit a warning when this is encountered and not be totally 'silent'?

jmcnamara added a commit that referenced this issue Sep 23, 2016

@jmcnamara

This comment has been minimized.

Show comment
Hide comment
@jmcnamara

jmcnamara Sep 23, 2016

Owner

I've pushed a version of my intended fix for this to master. There is now a new constructor option to remove tzinfo from datetimes/times:

from xlsxwriter.workbook import Workbook
from datetime import datetime
import pytz

workbook = Workbook('test.xlsx', {'remove_timezone': True})
worksheet = workbook.add_worksheet()
format1 = workbook.add_format({'num_format': 'yyyy-mm-dd'})


date1 = datetime.strptime('2016-09-12 12:00', "%Y-%m-%d %H:%M")
date1 = date1.replace(tzinfo=pytz.utc)

worksheet.write_datetime('A1', date1, format1)

workbook.close()

See the updated docs:

Timezone Handling
-----------------

Excel doesn't support timezones in datetimes/times so there isn't any failsafe
way that XlsxWriter can map a Python timezone aware datetime into an Excel
datetime. As such the user should handle the timezones in some way that makes
sense according to their requirements. Usually this will require some
conversion to a timezone adjusted time and the removal of the ``tzinfo`` from
the datetime object so that it can be passed to :func:`write_datetime`::

    utc_datetime = datetime(2016, 9, 23, 14, 13, 21, tzinfo=utc)
    naive_datetime = utc_datetime.replace(tzinfo=None)

    worksheet.write_datetime(row, 0, naive_datetime, date_format)

Alternatively the :func:`Workbook` constructor option ``remove_timezone`` can
be used to strip the timezone from datetime values passed to
:func:`write_datetime`. The default is ``False``. To enable this option use::

    workbook = xlsxwriter.Workbook(filename, {'remove_timezone': True})

When :ref:`ewx_pandas` you can pass the argument as follows::

    writer = pd.ExcelWriter('pandas_example.xlsx',
                            engine='xlsxwriter',
                            options={'remove_timezone': True})

Owner

jmcnamara commented Sep 23, 2016

I've pushed a version of my intended fix for this to master. There is now a new constructor option to remove tzinfo from datetimes/times:

from xlsxwriter.workbook import Workbook
from datetime import datetime
import pytz

workbook = Workbook('test.xlsx', {'remove_timezone': True})
worksheet = workbook.add_worksheet()
format1 = workbook.add_format({'num_format': 'yyyy-mm-dd'})


date1 = datetime.strptime('2016-09-12 12:00', "%Y-%m-%d %H:%M")
date1 = date1.replace(tzinfo=pytz.utc)

worksheet.write_datetime('A1', date1, format1)

workbook.close()

See the updated docs:

Timezone Handling
-----------------

Excel doesn't support timezones in datetimes/times so there isn't any failsafe
way that XlsxWriter can map a Python timezone aware datetime into an Excel
datetime. As such the user should handle the timezones in some way that makes
sense according to their requirements. Usually this will require some
conversion to a timezone adjusted time and the removal of the ``tzinfo`` from
the datetime object so that it can be passed to :func:`write_datetime`::

    utc_datetime = datetime(2016, 9, 23, 14, 13, 21, tzinfo=utc)
    naive_datetime = utc_datetime.replace(tzinfo=None)

    worksheet.write_datetime(row, 0, naive_datetime, date_format)

Alternatively the :func:`Workbook` constructor option ``remove_timezone`` can
be used to strip the timezone from datetime values passed to
:func:`write_datetime`. The default is ``False``. To enable this option use::

    workbook = xlsxwriter.Workbook(filename, {'remove_timezone': True})

When :ref:`ewx_pandas` you can pass the argument as follows::

    writer = pd.ExcelWriter('pandas_example.xlsx',
                            engine='xlsxwriter',
                            options={'remove_timezone': True})

@jmcnamara

This comment has been minimized.

Show comment
Hide comment
@jmcnamara

jmcnamara Dec 2, 2016

Owner

Fixed in version 0.94.

Owner

jmcnamara commented Dec 2, 2016

Fixed in version 0.94.

@jmcnamara jmcnamara closed this Dec 2, 2016

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment