1package Time::Piece::MySQL; 2use strict; 3use vars qw($VERSION); 4$VERSION = '0.06'; 5 6use Time::Piece; 7 8sub import { shift; @_ = ('Time::Piece', @_); goto &Time::Piece::import } 9 10package Time::Piece; 11 12use Time::Seconds; 13 14BEGIN 15{ 16 # I don't know what this dst bug is, but the code was here... 17 my $has_dst_bug = 18 Time::Piece->strptime( '20000601120000', '%Y %m %d %H %M %S' )->hour != 12; 19 sub HAS_DST_BUG () { $has_dst_bug } 20} 21 22sub mysql_date 23{ 24 my $self = shift; 25 my $old_sep = $self->date_separator('-'); 26 my $ymd = $self->ymd; 27 $self->date_separator($old_sep); 28 return $ymd; 29} 30 31sub mysql_time 32{ 33 my $self = shift; 34 my $old_sep = $self->time_separator(':'); 35 my $hms = $self->hms; 36 $self->time_separator($old_sep); 37 return $hms; 38} 39 40sub mysql_datetime 41{ 42 my $self = shift; 43 return join ' ', $self->mysql_date, $self->mysql_time; 44} 45 46 47# '1000-01-01 00:00:00' to '9999-12-31 23:59:59' 48 49sub from_mysql_date { 50 my ($class, $dt) = @_; 51 return unless $dt and $dt ge '1970' and $dt lt '2038'; 52 my $time = eval {$class->strptime($dt, '%Y-%m-%d')}; 53 return $time; 54} 55 56sub from_mysql_datetime { 57 my ($class, $dt) = @_; 58 return unless $dt and $dt ge '1970' and $dt lt '2038'; 59 my $time = eval {$class->strptime($dt, '%Y-%m-%d %H:%M:%S')}; 60 $time -= ONE_HOUR if HAS_DST_BUG && $time->isdst; 61 return $time; 62} 63 64sub mysql_timestamp { 65 my $self = shift; 66 return $self->strftime('%Y%m%d%H%M%S'); 67} 68 69sub from_mysql_timestamp { 70 # From MySQL version 4.1, timestamps are returned as datetime strings 71 my ($class, $timestamp) = @_; 72 my $length = length $timestamp; 73 return from_mysql_datetime(@_) if $length == 19; 74 # most timestamps have 2-digit years, except 8 and 14 char ones 75 if ( $length != 14 && $length != 8 ) { 76 $timestamp = (substr($timestamp, 0, 2) < 70 ? "20" : "19") 77 . $timestamp; 78 } 79 # now we need to extend this to 14 chars to make sure we get 80 # consistent cross-platform results 81 $timestamp .= substr("19700101000000", length $timestamp); 82 my $time = eval {$class->strptime( $timestamp, '%Y %m %d %H %M %S')}; 83 return $time; 84} 85 861; 87 88__END__ 89 90=head1 NAME 91 92Time::Piece::MySQL - Adds MySQL-specific methods to Time::Piece 93 94=head1 SYNOPSIS 95 96 use Time::Piece::MySQL; 97 98 my $time = localtime; 99 100 print $time->mysql_datetime; 101 print $time->mysql_date; 102 print $time->mysql_time; 103 104 my $time = Time::Piece->from_mysql_datetime( $mysql_datetime ); 105 my $time = Time::Piece->from_mysql_date( $mysql_date ); 106 my $time = Time::Piece->from_mysql_timestamp( $mysql_timestamp ); 107 108=head1 DESCRIPTION 109 110Using this module instead of, or in addition to, C<Time::Piece> adds a 111few MySQL-specific date-time methods to C<Time::Piece> objects. 112 113=head1 OBJECT METHODS 114 115=head2 mysql_date / mysql_time / mysql_datetime / mysql_timestamp 116 117Returns the date and/or time in a format suitable for use by MySQL. 118 119=head1 CONSTRUCTORS 120 121=head2 from_mysql_date / from_mysql_datetime / from_mysql_timestamp 122 123Given a date, datetime, or timestamp value as returned from MySQL, these 124constructors return a new Time::Piece object. If the value is NULL, they 125will retrun undef. 126 127=head2 CAVEAT 128 129C<Time::Piece> itself only works with times in the Unix epoch, this module has 130the same limitation. However, MySQL itself handles date and datetime columns 131from '1000-01-01' to '9999-12-31'. Feeding in times outside of the Unix epoch 132to any of the constructors has unpredictable results. 133 134Also, MySQL doesn't validate dates (because your application should); it only 135checks that dates are in the right format. So, your database might include 136dates like 2004-00-00 or 2001-02-31. Passing invalid dates to any of the 137constructors is a bad idea: on my system the former type (with zeros) returns 138undef (previous version used to die) while the latter returns a date in the 139following month. 140 141=head1 AUTHOR 142 143Original author: Dave Rolsky <autarch@urth.org> 144 145Current maintainer: Marty Pauley <marty+perl@kasei.com> 146 147=head1 COPYRIGHT 148 149(c) 2002 Dave Rolsky 150 151(c) 2004 Marty Pauley 152 153This program is free software; you can redistribute it and/or modify it under 154the same terms as Perl itself. 155 156=head1 SEE ALSO 157 158L<Time::Piece> 159 160=cut 161