diff -Naur DBIx-Class.orig/lib/DBIx/Class/InflateColumn/DateTime.pm DBIx-Class/lib/DBIx/Class/InflateColumn/DateTime.pm
--- DBIx-Class.orig/lib/DBIx/Class/InflateColumn/DateTime.pm 2008-02-07 12:29:23.000000000 +0100
+++ DBIx-Class/lib/DBIx/Class/InflateColumn/DateTime.pm 2008-05-30 15:17:23.000000000 +0200
@@ -30,6 +30,16 @@
starts_when => { data_type => 'datetime', extra => { timezone => "America/Chicago" } }
);
+data_type can be also 'timestamp', 'timestamptz':
+
+ __PACKAGE__->add_columns(
+ starts_when => { data_type => 'timestamp', extra => { timezone => "America/Chicago" } }
+ );
+
+ __PACKAGE__->add_columns(
+ starts_when => { data_type => 'timestamptz' }
+ );
+
=head1 DESCRIPTION
This module figures out the type of DateTime::Format::* class to
@@ -60,13 +70,15 @@
$self->next::method($column, $info, @rest);
return unless defined($info->{data_type});
my $type = lc($info->{data_type});
- $type = 'datetime' if ($type =~ /^timestamp/);
+ $type = 'timestamptz'
+ if $type eq 'timestamp with time zone';
+# $type = 'datetime' if ($type =~ /^timestamp/);
my $timezone;
if ( exists $info->{extra} and exists $info->{extra}{timezone} and defined $info->{extra}{timezone} ) {
$timezone = $info->{extra}{timezone};
}
- if ($type eq 'datetime' || $type eq 'date') {
+ if ($type eq 'datetime' || $type eq 'date' || $type eq 'timestamp' || $type eq 'timestamptz') {
my ($parse, $format) = ("parse_${type}", "format_${type}");
$self->inflate_column(
$column =>
diff -Naur DBIx-Class.orig/t/89inflate_datetime_timestamptz.t DBIx-Class/t/89inflate_datetime_timestamptz.t
--- DBIx-Class.orig/t/89inflate_datetime_timestamptz.t 1970-01-01 01:00:00.000000000 +0100
+++ DBIx-Class/t/89inflate_datetime_timestamptz.t 2008-05-30 15:24:12.000000000 +0200
@@ -0,0 +1,123 @@
+#!/usr/bin/perl
+
+use strict;
+use warnings;
+
+use Test::More;
+
+use FindBin qw($Bin);
+use lib "$Bin/lib";
+
+BEGIN {
+ plan('skip_all' => 'please set $ENV{"DBICTEST_DSN"}, $ENV{"DBICTEST_DBUSER"}, $ENV{"DBICTEST_DBPASS"} to run the tests')
+ if not $ENV{"DBICTEST_DSN"};
+}
+
+#plan 'no_plan';
+plan 'tests' => 14;
+
+exit main();
+
+sub main {
+ use_ok( 'DBICTestTimestamp' ) or exit;
+
+ my $db = DBICTestTimestamp->connect(
+ $ENV{"DBICTEST_DSN"},
+ $ENV{"DBICTEST_DBUSER"},
+ $ENV{"DBICTEST_DBPASS"},
+ );
+
+ my $db_driver_name = $db->storage->dbh->{'Driver'}->{'Name'};
+
+ # cleanup
+ eval {
+ $db->storage->dbh->do('DROP TABLE timestamp_test');
+ };
+ $db->deploy;
+
+ my $tst_rs = $db->resultset('DBICTestTimestamp::TimestampTZ');
+
+ my $ts = $tst_rs->create({
+ 'timestamp_test_id' => 1,
+ 'ts' => DateTime->now('time_zone' => 'UTC'),
+ });
+ ok($ts, 'create with ts');
+
+ my $utc_hour = DateTime->now('time_zone' => 'UTC')->hour;
+ diag('now is '.DateTime->now('time_zone' => 'UTC')->strftime('%a, %d %b %Y %H:%M:%S %z'));
+
+ # fetch the row back
+ my ($ts2) = $tst_rs->search({ 'timestamp_test_id' => $ts->timestamp_test_id });
+
+ is($ts2->tstz, undef, 'NULL rows return undef');
+ isa_ok($ts2->ts, 'DateTime', 'not NULL DateTime object');
+
+ cmp_ok(
+ $ts2->ts->time_zone->name,
+ 'ne',
+ 'floating',
+ 'timezone should be set ('.$ts2->ts->time_zone->name.'), no floating'
+ );
+
+ cmp_ok(
+ $ts2->ts->hour,
+ '!=',
+ $utc_hour,
+ 'hour should be in the timezone not UTC'
+ );
+
+ SKIP: {
+ skip 'datetime parser can not do parse_timestamptz', 4
+ if !$db->storage->datetime_parser->can('parse_timestamptz');
+
+ # insert row with timestamp and timezone
+ my $tstz = $tst_rs->create({
+ 'timestamp_test_id' => 2,
+ 'tstz' => DateTime->now->set_time_zone('America/Chicago'),
+ });
+ ok($tstz, 'create with tstz');
+
+ # fetch the row back
+ my ($tstz2) = $tst_rs->search({ 'timestamp_test_id' => $tstz->timestamp_test_id });
+ is($tstz2->ts, undef, 'NULL rows return undef');
+ isa_ok($tstz2->tstz, 'DateTime', 'not NULL DateTime object');
+
+ if ($db_driver_name eq 'Pg') {
+ # When a timestamp with time zone value is output, it is always converted from UTC
+ # to the current timezone zone, and displayed as local time in that zone.
+ #
http://www.postgresql.org/docs/8.2/interactive/datatype-datetime.html
+ cmp_ok(
+ $tstz2->tstz->time_zone->name,
+ 'ne',
+ 'floating',
+ 'timezone should be set ('.$tstz2->tstz->time_zone->name.'), no floating'
+ );
+ }
+ else {
+ is($tstz2->tstz->time_zone->name, '-0500', 'time zone kept');
+ }
+ }
+
+ # insert row with datetime
+ my $d = $tst_rs->create({
+ 'timestamp_test_id' => 3,
+ 'd' => DateTime->now,
+ });
+ ok($d, 'create with date');
+
+ # fetch the row back
+ my ($d2) = $tst_rs->search({ 'timestamp_test_id' => $d->timestamp_test_id });
+
+ is($d2->tstz, undef, 'NULL rows return undef');
+ isa_ok($d2->d, 'DateTime', 'not NULL DateTime object');
+
+ cmp_ok(
+ $d2->d->time_zone->name,
+ 'ne',
+ 'floating',
+ 'timezone should be set ('.$d2->d->time_zone->name.'), no floating'
+ );
+
+ return 0;
+}
+
diff -Naur DBIx-Class.orig/t/lib/DBICTestTimestamp/TimestampTZ.pm DBIx-Class/t/lib/DBICTestTimestamp/TimestampTZ.pm
--- DBIx-Class.orig/t/lib/DBICTestTimestamp/TimestampTZ.pm 1970-01-01 01:00:00.000000000 +0100
+++ DBIx-Class/t/lib/DBICTestTimestamp/TimestampTZ.pm 2008-05-30 15:23:08.000000000 +0200
@@ -0,0 +1,55 @@
+package DBICTestTimestamp::TimestampTZ;
+
+=head1 NAME
+
+COIN::Curator::DBIC::Test::TimestampTest - testing table to play with timestamps
+
+=head1 SYNOPSIS
+
+=cut
+
+use strict;
+use warnings;
+
+use base qw/DBIx::Class/;
+
+=head1 DESCRIPTION
+
+=head2 table
+
+ timestamp_test
+
+=head2 columns
+
+ timestamp_test_id - PL
+ ts => { data_type => 'datetime', extra => { timezone => 'Europe/Vienna' }},
+ tstz => { data_type => 'datetime' },
+
+=cut
+
+__PACKAGE__->load_components(qw/PK::Auto InflateColumn::DateTime ResultSetManager Core/);
+__PACKAGE__->table('timestamp_test');
+__PACKAGE__->add_columns(
+ timestamp_test_id => { data_type => 'int' },
+ ts => { data_type => 'timestamp', extra => { timezone => 'Europe/Vienna' }, 'is_nullable' => 1},
+ tstz => { data_type => 'timestamp with time zone', 'is_nullable' => 1 },
+ d => { data_type => 'datetime', extra => { timezone => 'Europe/Vienna' }, 'is_nullable' => 1},
+);
+__PACKAGE__->set_primary_key('timestamp_test_id');
+
+
+=head1 METHODS
+
+=cut
+
+1;
+
+
+__END__
+
+=head1 AUTHOR
+
+Jozef Kutej
+
+=cut
+
diff -Naur DBIx-Class.orig/t/lib/DBICTestTimestamp.pm DBIx-Class/t/lib/DBICTestTimestamp.pm
--- DBIx-Class.orig/t/lib/DBICTestTimestamp.pm 1970-01-01 01:00:00.000000000 +0100
+++ DBIx-Class/t/lib/DBICTestTimestamp.pm 2008-05-30 15:23:08.000000000 +0200
@@ -0,0 +1,56 @@
+package DBICTestTimestamp;
+
+=head1 NAME
+
+DBICTestTimestamp - DBIC timestamp test schema
+
+=cut
+
+use strict;
+use warnings;
+
+our $VERSION = '0.01';
+
+use base qw/DBIx::Class::Schema/;
+
+
+
+__PACKAGE__->load_classes(qw{
+ TimestampTZ
+});
+
+sub connect {
+ my $class = shift;
+
+ my $self = $class->SUPER::connect(@_);
+
+ # set the date, timestamp and timestamp with timezone format for Oracle
+ if ($self->storage->dbh->{'Driver'}->{'Name'} eq 'Oracle') {
+ eval 'use DateTime::Format::Oracle;';
+ die 'need DateTime::Format::Oracle for Oracle timestamp tests - '.$@ if $@;
+
+ $self->storage->dbh->do(
+ "alter session set nls_date_format = '"
+ .DateTime::Format::Oracle->nls_date_format
+ ."'"
+ );
+ $self->storage->dbh->do(
+ "alter session set nls_timestamp_format = '"
+ .DateTime::Format::Oracle->nls_timestamp_format
+ ."'"
+ );
+
+ # timestamptz not jet implemented in DateTime::Format::Oracle, hopefully in the future...
+ if (DateTime::Format::Oracle->can('nls_timestamp_tz_format')) {
+ $self->storage->dbh->do(
+ "alter session set nls_timestamp_tz_format = '"
+ .DateTime::Format::Oracle->nls_timestamp_tz_format
+ ."'"
+ );
+ }
+ }
+
+ return $self;
+}
+
+1;
