Dates and times

Specify a date format

By default agate will attempt to guess the format of a Date or DateTime column. In some cases, it may not be possible to automatically figure out the format of a date. In this case you can specify a datetime.datetime.strptime() formatting string to specify how the dates should be parsed. For example, if your dates were formatted as “15-03-15” (March 15th, 2015) then you could specify:

date_type = agate.Date('%d-%m-%y')

Another use for this feature is if you have a column that contains extraneous data. For instance, imagine that your column contains hours and minutes, but they are always zero. It would make more sense to load that data as type Date and ignore the extra time information:

date_type = agate.Date('%m/%d/%Y 00:00')

Specify a timezone

Timezones are hard. Under normal circumstances (no arguments specified), agate will not try to parse timezone information, nor will it apply a timezone to the datetime.datetime instances it creates. (They will be naive in Python parlance.) There are two ways to force timezone data into your agate columns.

The first is to use a format string, as shown above, and specify a pattern for timezone information:

datetime_type = agate.DateTime('%Y-%m-%d %H:%M:%S%z')

The second way is to specify a timezone as an argument to the type constructor:

import pytz

eastern = pytz.timezone('US/Eastern')
datetime_type = agate.DateTime(timezone=eastern)

In this case all timezones that are processed will be set to have the Eastern timezone. Note, the timezone will be set, not converted. You cannot use this method to convert your timezones from UTC to another timezone. To do that see Convert timezones.

Calculate a time difference

See Difference between dates.

Sort by date

See By date.

Convert timezones

If you load data from a spreadsheet in one timezone and you need to convert it to another, you can do this using a Formula. Your datetime column must have timezone data for the following example to work. See Specify a timezone.

import pytz

us_eastern = pytz.timezone('US/Eastern')
datetime_type = agate.DateTime(timezone=us_eastern)

column_names = ['what', 'when']
column_types = [text_type, datetime_type]

table = agate.Table.from_csv('events.csv', columns)

rome = timezone('Europe/Rome')
timezone_shifter = agate.Formula(lambda r: r['when'].astimezone(rome))

table = agate.Table.compute([
    ('when_in_rome', timezone_shifter)
])