469,292 Members | 1,310 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,292 developers. It's quick & easy.

Converting a date/timestamp

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.
Jul 6 '07 #1
7 12728
numberwhun
3,503 Expert Mod 2GB
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.

Expand|Select|Wrap|Line Numbers
  1. ##########################################################################
  2. #  Date information for time stamps
  3. ##########################################################################
  4. my $dtstamp;
  5. my @months = qw(01 02 03 04 05 06 07 08 09 10 11 12);
  6. my @weekDays = qw(01 02 03 04 05 06 07);
  7. (my $second, my $minute, my $hour, my $dayOfMonth, my $month, my $yearOffset, my $dayOfWeek, my $dayOfYear, my $daylightSavings) = localtime();
  8. my $year = 1900 + $yearOffset;
  9. my $theTime = "$hour:$minute:$second, $weekDays[$dayOfWeek] $months[$month] $dayOfMonth, $year";
  10.  
  11. if ( $dayOfMonth > 9 )
  12.    my $dt = $months[$month] . "/" . $dayOfMonth . "/" . $year; 
  13.    $dtstamp = $year . $months[$month] . $dayOfMonth;
  14.    $datetime = $year . $months[$month] . $dayOfMonth . "." . $hour . $minute . $second;
  15. }
  16. else
  17. {
  18.    my $dt = $months[$month] . "/" . '0'.$dayOfMonth . "/" . $year; 
  19.    $dtstamp = $year . $months[$month] . '0'.$dayOfMonth;
  20.    $datetime = $year . $months[$month] . '0'.$dayOfMonth . "." . $hour . $minute . $second;
  21. }
  22.  
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
Jul 6 '07 #2
KevinADC
4,059 Expert 2GB
Look into using Time::Local

perldoc Time::Local

if you get stuck, ask more questions.
Jul 6 '07 #3
miller
1,089 Expert 1GB
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:

Expand|Select|Wrap|Line Numbers
  1. my $sth = $conn->prepare(q{SELECT DATE_FORMAT(date, "%a, %e %b %Y %H:%i:%S"), title, summary FROM news});
  2. $sth->execute() or die $conn->errstr();
  3.  
- Miller
Jul 6 '07 #4
KevinADC
4,059 Expert 2GB
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.
Jul 6 '07 #5
numberwhun
3,503 Expert Mod 2GB
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
Jul 6 '07 #6
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();
Jul 7 '07 #7
KevinADC
4,059 Expert 2GB
Very good, thanks for the follow up.

Kevin
Jul 7 '07 #8

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

6 posts views Thread by Kit | last post: by
1 post views Thread by news.microsoft.com | last post: by
3 posts views Thread by Tgone | last post: by
5 posts views Thread by Hemant Shah | last post: by
reply views Thread by suresh191 | last post: by
1 post views Thread by Geralt96 | last post: by
reply views Thread by harlem98 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.