The Perl and Raku Conference 2025: Greenville, South Carolina - June 27-29 Learn more

KASEI / Time-Piece-MySQL-0.06 / lib / Time / Piece / MySQL.pm 

            

              
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
—
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160

              
package Time::Piece::MySQL;
use strict;
use vars qw($VERSION);
$VERSION = '0.06';
use Time::Piece;
sub import { shift; @_ = ('Time::Piece', @_); goto &Time::Piece::import }
package Time::Piece;
use Time::Seconds;
BEGIN
{
    # I don't know what this dst bug is, but the code was here...
    my $has_dst_bug =
        Time::Piece->strptime( '20000601120000', '%Y %m %d %H %M %S' )->hour != 12;
    sub HAS_DST_BUG () { $has_dst_bug }
}
sub mysql_date
{
    my $self = shift;
    my $old_sep = $self->date_separator('-');
    my $ymd = $self->ymd;
    $self->date_separator($old_sep);
    return $ymd;
}
sub mysql_time
{
    my $self = shift;
    my $old_sep = $self->time_separator(':');
    my $hms = $self->hms;
    $self->time_separator($old_sep);
    return $hms;
}
sub mysql_datetime
{
    my $self = shift;
    return join ' ', $self->mysql_date, $self->mysql_time;
}
# '1000-01-01 00:00:00' to '9999-12-31 23:59:59'
sub from_mysql_date {
    my ($class, $dt) = @_;
    return unless $dt and $dt ge '1970' and $dt lt '2038';
    my $time = eval {$class->strptime($dt, '%Y-%m-%d')};
    return $time;
}
sub from_mysql_datetime {
    my ($class, $dt) = @_;
    return unless $dt and $dt ge '1970' and $dt lt '2038';
    my $time = eval {$class->strptime($dt, '%Y-%m-%d %H:%M:%S')};
    $time -= ONE_HOUR if HAS_DST_BUG && $time->isdst;
    return $time;
}
sub mysql_timestamp {
        my $self = shift;
        return $self->strftime('%Y%m%d%H%M%S');
}
sub from_mysql_timestamp {
    # From MySQL version 4.1, timestamps are returned as datetime strings
    my ($class, $timestamp) = @_;
    my $length = length $timestamp;
    return from_mysql_datetime(@_) if $length == 19;
    # most timestamps have 2-digit years, except 8 and 14 char ones
    if ( $length != 14 && $length != 8 ) {
        $timestamp = (substr($timestamp, 0, 2) < 70 ? "20" : "19")
                   . $timestamp;
    }
    # now we need to extend this to 14 chars to make sure we get
    # consistent cross-platform results
    $timestamp .= substr("19700101000000", length $timestamp);
    my $time = eval {$class->strptime( $timestamp, '%Y %m %d %H %M %S')};
    return $time;
}
1;
__END__
=head1 NAME
Time::Piece::MySQL - Adds MySQL-specific methods to Time::Piece
=head1 SYNOPSIS
  use Time::Piece::MySQL;
  my $time = localtime;
  print $time->mysql_datetime;
  print $time->mysql_date;
  print $time->mysql_time;
  my $time = Time::Piece->from_mysql_datetime( $mysql_datetime );
  my $time = Time::Piece->from_mysql_date( $mysql_date );
  my $time = Time::Piece->from_mysql_timestamp( $mysql_timestamp );
=head1 DESCRIPTION
Using this module instead of, or in addition to, C<Time::Piece> adds a
few MySQL-specific date-time methods to C<Time::Piece> objects.
=head1 OBJECT METHODS
=head2 mysql_date / mysql_time / mysql_datetime / mysql_timestamp
Returns the date and/or time in a format suitable for use by MySQL.
=head1 CONSTRUCTORS
=head2 from_mysql_date / from_mysql_datetime / from_mysql_timestamp
Given a date, datetime, or timestamp value as returned from MySQL, these
constructors return a new Time::Piece object.  If the value is NULL, they
will retrun undef.
=head2 CAVEAT
C<Time::Piece> itself only works with times in the Unix epoch, this module has
the same limitation.  However, MySQL itself handles date and datetime columns
from '1000-01-01' to '9999-12-31'.  Feeding in times outside of the Unix epoch
to any of the constructors has unpredictable results.
Also, MySQL doesn't validate dates (because your application should); it only
checks that dates are in the right format.  So, your database might include
dates like 2004-00-00 or 2001-02-31.  Passing invalid dates to any of the
constructors is a bad idea: on my system the former type (with zeros) returns
undef (previous version used to die) while the latter returns a date in the
following month.
=head1 AUTHOR
Original author: Dave Rolsky <autarch@urth.org>
Current maintainer: Marty Pauley <marty+perl@kasei.com>
=head1 COPYRIGHT
(c) 2002 Dave Rolsky
(c) 2004 Marty Pauley
This program is free software; you can redistribute it and/or modify it under
the same terms as Perl itself.
=head1 SEE ALSO
L<Time::Piece>
=cut