A quick date/Time fix (MySql)

By Kelicula on Sep 12th, 2008 4:55 am

Ever had to fix auto-generated dates retrieved from MySql databases?

I made a really quick fix that displays the date in "human readable" format.
Just replace "CST" with GMT, or EST etc...
Whatever time zone your mysql server is located.

it takes one argument, the date from the database.

Usage:

use strict;
use DBI;

my $dbh = DBI->connect("DBI:mysql:schemaName", "user", "password")|| die "Error: $DBI::errstr";

my $results = $dbh->fetchall_arrayref(qq~SELECT date FROM table~);

for(@{$results}){
print &fixDate($_[0])."\n"; # See below for subroutine...
}

$dbh->disconnect;

Possible output:

09/05/2008 09:15pm CST
09/02/2008 11:35am CST
etc...

sub fixDate {

my @it = split(/-/, shift);
my @ti = split(/ /,$it[2]);

my @time = split(/:/, $ti[1]);
my $suffix = 'am';

if($time[0] > 12){
$time[0] -= 12;
$suffix = 'pm';
}
if($time[0] == 00){
$time[0] = 12;
$suffix = 'am';
}
elsif($time[0] == 12){
$suffix = 'pm';
}
return "$it[1]/$ti[0]/$it[0] $time[0]:$time[1]$suffix CST";
}

3 Years Ago

I spotted an error. I apologize.
The correct $dbh method is "selectall_arrayref" as opposed to "fetchall_arrayref", however the subroutine works fine, once the date has been retrieved correctly.

Cheers

Kelicula

Newbie Poster

17 posts since Jul 2007

Reputation Points: 10

Solved Threads: 1

3 Years Ago

You should put all the code in the same post. In your function you should use if/elsif/else instead of if/if/elsif.

KevinADC

Posting Shark

921 posts since Mar 2006

Reputation Points: 246

Solved Threads: 67

3 Years Ago

In my way of thinking there would be no 12pm if I stated: if/elsif/else but I see what you mean!
TMTOWTDI

if($time[0] > 12){
$time[0] -= 12;
$suffix = 'pm';
}
elsif($time[0] == 00){
$time[0] = 12;
$suffix = 'am';
}
else{
$suffix = 'am';
}
return "$it[1]/$ti[0]/$it[0] $time[0]:$time[1]$suffix CST";
}

Kelicula

Newbie Poster

17 posts since Jul 2007

Reputation Points: 10

Solved Threads: 1

3 Years Ago

I think this is the fastest code...

sub fixDate {

my $class = shift;

my @it = split(/-/, shift);
my @ti = split(/ /,$it[2]);

my @time = split(/:/, $ti[1]);
my $suffix = 'am';

if($time[0] > 12){
$time[0] -= 12;
$suffix = 'pm';
}
elsif($time[0] == 00){
$time[0] = 12;
$suffix = 'am';
}

return "$it[1]/$ti[0]/$it[0] $time[0]:$time[1]$suffix CST";
}

Gotta love Perl's garbage collector. (compiler)

Kelicula

Newbie Poster

17 posts since Jul 2007

Reputation Points: 10

Solved Threads: 1

This article has been dead for over three months

You