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¶
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)
])