Saving Time Zones..and your hair!

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.

5658ebc05c9ba0563fba8d5f0999b32a?s=128

Noel Maddy

June 09, 2015
Tweet

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)
  9. None
  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