Saving Time Zones..and your hair!

Introducing Perl module DBIx::Class::InflateColumn::DateTime::WithTimeZone that allows saving and retrieving of DateTime time zones transparently in a database.

Transcript

1. Saving Time Zones ...and your hair! Noel Maddy (Zhtwn) zhtwnpanta@gmail.com
2. How many of you • Use dates and times in Perl?
3. How many of you • Use dates and times in Perl? • Use DateTime?
4. How many of you • Use dates and times in Perl? • Use DateTime? • Store them in a database?
5. How many of you • Use dates and times in Perl? • Use DateTime? • Store them in a database? • Use DBIx::Class?
6. How many of you • Use dates and times in Perl? • Use DateTime? • Store them in a database? • Use DBIx::Class? • Hate time zones?
7. __PACKAGE__->load_components('InflateColumn::DateTime'); __PACKAGE__->add_columns( dt => { data_type => 'timestamp' } ); … $dt = DateTime->now( time_zone => 'America/Chicago' ); # e.g., 2015-06-09 13:01:05, America/Chicago $row->create( { dt => $now } ); … # retreived row say $row->dt . ''; say $row->date_time->name; What do you get?
8. I saved: 2015-06-09 13:01:05, America/Chicago SQLite returns: 2015-06-09 08:01:05, UTC MySQL returns: 2015-06-09 13:01:05, floating Postgres returns: 2015-06-09 13:01:05, floating (TIMESTAMP column) 2015-06-09 14:01:05, -0400 (TIMESTAMP WITH TIME ZONE)
10. DBIx::Class::InflateColumn::DateTime::WithTimeZone https://gitweb.com/Zhtwn/DBIx-Class-InflateColumn-DateTime-WithTimeZone
11. __PACKAGE__->load_components( 'InflateColumn::DateTime::WithTimeZone'); __PACKAGE__->add_columns( dt => { data_type => 'timestamp', timezone_source => 'tz' }, tz => { data_type => 'varchar', size => 40 }, ); … $dt = DateTime->now( time_zone => 'America/Chicago' ); # e.g., 2015-06-09 13:01:05, America/Chicago $row->create( { dt => $now } ); … # retreived row now matches what's saved say $row->dt . ''; # 2015-06-09T13:01:05 say $row->date_time->name; # America/Chicago Whew!
12. Behind the Scenes • On deflation • time zone stored in timezone_source column • DateTime converted to UTC • On inflation • DateTime created in UTC • time zone set from timezone_source column
13. More Details • Nullable column status must match • Time zone column must be wide enough • Read the docs?
14. drolsky++ ribasushi++ DST--
15. DBIx::Class::InflateColumn::DateTime::WithTimeZone https://github.com/Zhtwn/DBIx-Class-InflateColumn-DateTime-WithTimeZone On CPAN very soon Noel Maddy (Zhtwn) zhtwnpanta@gmail.com