Converting a date/timestamp

[bdbeames]

I never used Perl before, so I need a little help formating a date.
What I'm doing is querying a postgres database and then creating a .xml file for a RSS feed. I don't know how to correctly format the timestamp for my .xml file.

Let say I query the database and then store the values in variables. I then print the variables to an .xml file. Here is an example:

my $sth = $conn->prepare("SELECT date,title,summary From news");
$sth->execute() or die "execute failed: " . $sth->errstr();
my($date, $title,$sum);

while (($date,$title,$sum) = $sth->fetchrow()) {
print XML " <lastBuildDate>$date</lastBuildDate>\n";
}

How do I create the correct format that I need when I print $date?

The format of the timestamp in $date that I get from the database is:
2007-07-02 11:30:01.71055
I need to reformat it to look like:
Tue, 19 Dec 2006 20:15:00 MST

Could someone show me how to do this? I've search on-line for a few hours with no luck.

[numberwhun]

First, Perl is an awesome language and in my opinion, the best there is. If you are going to be coding in Perl, I recommend you pick up "Learning Perl" from O'Reilly. The latest one is 4th edition and is authored by Randal Schwartz, Tom Phoenix, and Brian D Foy.

As far as date time stamps, I have not delved into gathering them from a database. Instead, I use the following code that allows me the freedom to format it the way I wish. Feel free to modify it to meet your needs.

##########################################################################
#  Date information for time stamps
##########################################################################
my $dtstamp;
my @months = qw(01 02 03 04 05 06 07 08 09 10 11 12);
my @weekDays = qw(01 02 03 04 05 06 07);
(my $second, my $minute, my $hour, my $dayOfMonth, my $month, my $yearOffset, my $dayOfWeek, my $dayOfYear, my $daylightSavings) = localtime();
my $year = 1900 + $yearOffset;
my $theTime = "$hour:$minute:$second, $weekDays[$dayOfWeek] $months[$month] $dayOfMonth, $year";
 
if ( $dayOfMonth > 9 )
{ 
   my $dt = $months[$month] . "/" . $dayOfMonth . "/" . $year; 
   $dtstamp = $year . $months[$month] . $dayOfMonth;
   $datetime = $year . $months[$month] . $dayOfMonth . "." . $hour . $minute . $second;
}
else
{
   my $dt = $months[$month] . "/" . '0'.$dayOfMonth . "/" . $year; 
   $dtstamp = $year . $months[$month] . '0'.$dayOfMonth;
   $datetime = $year . $months[$month] . '0'.$dayOfMonth . "." . $hour . $minute . $second;
}
 

For more information on date time stamping, see the following perldoc reference page: http://perldoc.perl.org/functions/localtime.html

I hope that this helps.

Regards,

Jeff

[KevinADC]

Look into using Time::Local

perldoc Time::Local

if you get stuck, ask more questions.

[miller]

How do I create the correct format that I need when I print $date?

The format of the timestamp in $date that I get from the database is:
2007-07-02 11:30:01.71055
I need to reformat it to look like:
Tue, 19 Dec 2006 20:15:00 MST

Could someone show me how to do this? I've search on-line for a few hours with no luck.

What database are you using? I would suggest that in this circumstance that you simply let the database do the formatting for you. Here is a link to the documentation for MySQL 5.1

mysql5.1 Ref Man: Date and Time Functions

Changing your code to the following:

my $sth = $conn->prepare(q{SELECT DATE_FORMAT(date, "%a, %e %b %Y %H:%i:%S"), title, summary FROM news});
$sth->execute() or die $conn->errstr();
 

- Miller

[KevinADC]

What database are you using? I would suggest that in this circumstance that you simply let the database do the formatting for you. Here is a link to the documentation for MySQL 5.1

- Miller

That is a really good suggestion. I need to learn more about databases.

[numberwhun]

That is a really good suggestion. I need to learn more about databases.

You and me both. I know how to form SQL statements, that's easy. But have must really sit down and learn MySQL.

Regards,

Jeff

[bdbeames]

Thanks for all the help everyone.

I tried Numberwhun nice perl conversion. Great script, but not if you need to query the database. I am using it everytime I recreate my .xml file in the pubdate field.

Time::Local was great too, but not for my situtation.

I ended up changing my database query to convert the timestamp to a char. He is the query if anyone cares. This is on a postgres database.

my $sth = $conn->prepare("SELECT newsid,to_char(date, 'FMDy, DD Mon YYYY HH24:MI:SS'),title,summary FROM news ORDER BY newsid DESC")
or die "prepare failed: " . $conn->errstr();

[KevinADC]

Very good, thanks for the follow up.

Kevin