Perl's time and MySQL's timestamp

powerhouse has asked for the wisdom of the Perl Monks concerning the following question:

Ok, Wise ones ;o)

One more question for the early morning, until after I take a nap..... :o)

In MySQL the `timestamp` ends up putting a "timestamp" that appears to be the date "YearMonthDayHourMinuteSeconds" in that format(or close to that format). So, when I have a field that just gets a timestamp, and it has that, when I have Perl "thumb" through the records, looking for a timeframe period, how would I do it?

I just don't understand the "concept" of how Perl compares localtime with MySQL's timestamp format. Can someone explain how it works for me, or point me to the right place to find the answer? I've looked on the MySQL docs but cannot find the right answer/documentation on/for it.

I'd really appreciate it.

Thx,
Richard.

Comment on Perl's time and MySQL's timestamp

Replies are listed 'Best First'.
Re: Perl's time and MySQL's timestamp by gmax (Abbot) on Feb 05, 2003 at 09:54 UTC
If you need to search for specific dates or date ranges, the database engine is better equipped than bare Perl. `SELECT your_fields FROM your_table WHERE Timestamp_field BETWEEN "2003-02-01 10:30" AND "2003-02-05 12:00"` [download] Moreover, MySQL has a large collection of date-oriented functions, which you should consider before burdening your client with tasks that should belong in the server. However, if you really must, you can extract from a timestamp value the same fields that you get from localtime. #!/usr/bin/perl -w use strict; my ($sec,$min,$hour,$mday,$mon,$year) = localtime(1044436437); # Wed Feb 5 10:13:57 2003 $year += 1900; $mon++; my $timestamp = "20030205101357"; my ($tyear, $tmon, $tmday, $thour,$tmin, $tsec) = $timestamp =~ /(\d{4})(\d{2})(\d{2})(\d{2})(\d{2})(\d{2})/; printf " Time Timestamp\n"; printf "year %4d %4d\n", $year, $tyear; printf "month %4d %4d\n", $mon, $tmon; printf "day %4d %4d\n", $mday, $tmday; printf "hour %4d %4d\n", $hour, $thour; printf "min %4d %4d\n", $min, $tmin; printf "sec %4d %4d\n", $sec, $tsec; [download] Having done that, you can easily compare fields coming from localtime with the ones coming from timestamps. For complicated operations, you may want to trust some modules, such as Date::Calc or Date::Manip `_ _ _ _ (_\|\| \| \|(_\|>< _\|` [download]	[reply] [d/l] [select]
Re: Perl's time and MySQL's timestamp by iguanodon (Priest) on Feb 05, 2003 at 10:32 UTC
If you use Date::Manip, you can set the Internal flag that tells it to return date-time strings without colons, which happens to be the same format that MySQL uses for timestamps: `use Date::Manip; Date_Init("Internal=1"); # No colons in dates, same as mysql my $end = ParseDate('today'); my $start = DateCalc('today', '- 5 days');` [download]	[reply] [d/l]
Re: Perl's time and MySQL's timestamp by dws (Chancellor) on Feb 05, 2003 at 19:04 UTC
I've looked on the MySQL docs but cannot find the right answer/documentation on/for it. A side note: I'm finding that Paul DuBois's The MySQL Cookbook is an invaluable adjunct to the MySQL docs. His section on dates, times, datatimes, and timestamps explains things better than the MySQL docs, IMHO.	[reply]