A quick date/Time fix (MySql)

Kelicula 0 Tallied Votes 216 Views Share

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";
}
Kelicula 0 Newbie Poster

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

KevinADC 192 Practically a Posting Shark

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

Kelicula 0 Newbie Poster

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 0 Newbie Poster

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)

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.