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