Velocity Reviews - Computer Hardware Reviews

Velocity Reviews > Newsgroups > Programming > Perl > Perl Misc > Perl time and Mysql time

Reply
Thread Tools

Perl time and Mysql time

 
 
phal
Guest
Posts: n/a
 
      09-27-2005
HI, All

I have a problem to compare the time between current time in Perl and
MySQL time, I use to compare the time according to the Year, after that
the month, and day, and hour and minutes.

The following is the way on how I compare the time

if (current_year < data_year) then ERROR_YEAR
elsif (current_year >= data_year) then OKAY_YEAR

if(OKAY_YEAR) then
if(current_month < data_month) then ERROR_MONTH
elseif(current_month >= data_month) then OKAY_MONTH

if(OKAY_MONTH) then
if(current_day < data_day) then ERROR_DAY
elseif(current_day >= data_day) then OKAY_DAY

if(OKAY_DAY) then
if(current_hour < data_hour) then ERROR_HOUR
elseif(current_hour >= data_hour) then OKAY_HOUR

if(OKAY_HOUR) then
....

Okay, I think you will understand what the alogrithm going to do, it
isn't a good ways to compare, coz, I know some guru who use a good
alogrithm to compare better then mind.

Pleas help,
Thank

 
Reply With Quote
 
 
 
 
Hal Vaughan
Guest
Posts: n/a
 
      09-27-2005
phal wrote:

> HI, All
>
> I have a problem to compare the time between current time in Perl and
> MySQL time, I use to compare the time according to the Year, after that
> the month, and day, and hour and minutes.


For a lot of what I'm doing, it helps me to keep the dates and times in some
kind of human readable form. I don't know if you're getting the dates from
MySQL or just reading dates from a table, but I'll tell you how I handle
it. I keep my dates in the format "YYYY-MMDD-HHMMSS". While MySQL doesn't
see this as a date, I can store it in a regular char() or varchar() field
and, since this goes from year to second, I can easily and quickly compare
with an if statement in Perl, or with <,>,<=,>= in SQL. I've written a
series of simple routines that will take dates in Month, DD, YY (or YYYY)
format, or from the time functions in Perl and convert them to the format I
mentioned. You can do that, or use the DateTime module on CPAN to convert
to many formats (and I think DateTime adds times and compares as well).

You'll go nuts trying to compare the year, then month, then day... and so on
(unless you write just one routine to do it, as you should). Overall, it
really helps me to be able to keep all my dates in a human readable format
like I described, especially while debugging and using print statements to
track what's happening.

Hal
 
Reply With Quote
 
 
 
 
phal
Guest
Posts: n/a
 
      09-27-2005
Hi Hal;

I store the datetime in Mysql as "Time"= timestamp(14), which is
ordered as YYYYMMDDHHMMSS, and also store the " Duration " and I use it
to compare with the current time make by use Time::Local in Perl. If
the Time+Duration equl the current_time, the form will display expire,
and nothing else display.

As the time increase second by second, the program has to keep compare
with the time in the database, and compare it till the time reach the
current time and then expire.

This is what I want to say for the problem I encounter.

Thank lots

Phal

 
Reply With Quote
 
Brian Wakem
Guest
Posts: n/a
 
      09-27-2005
phal wrote:

> HI, All
>
> I have a problem to compare the time between current time in Perl and
> MySQL time, I use to compare the time according to the Year, after that
> the month, and day, and hour and minutes.
>
> The following is the way on how I compare the time
>
> if (current_year < data_year) then ERROR_YEAR
> elsif (current_year >= data_year) then OKAY_YEAR
>
> if(OKAY_YEAR) then
> if(current_month < data_month) then ERROR_MONTH
> elseif(current_month >= data_month) then OKAY_MONTH
>
> if(OKAY_MONTH) then
> if(current_day < data_day) then ERROR_DAY
> elseif(current_day >= data_day) then OKAY_DAY
>
> if(OKAY_DAY) then
> if(current_hour < data_hour) then ERROR_HOUR
> elseif(current_hour >= data_hour) then OKAY_HOUR
>
> if(OKAY_HOUR) then
> ....
>
> Okay, I think you will understand what the alogrithm going to do, it
> isn't a good ways to compare, coz, I know some guru who use a good
> alogrithm to compare better then mind.
>
> Pleas help,
> Thank



I usually store time as seconds since epoch, which is also Perl's time so
comparison is trivial.


--
Brian Wakem
Email: http://homepage.ntlworld.com/b.wakem/myemail.png
 
Reply With Quote
 
Chad Hanna
Guest
Posts: n/a
 
      09-27-2005
In message <(E-Mail Removed). com>, phal
<(E-Mail Removed)> writes
>HI, All
>
>I have a problem to compare the time between current time in Perl and
>MySQL time, I use to compare the time according to the Year, after that
>the month, and day, and hour and minutes.
>
>The following is the way on how I compare the time
>
>if (current_year < data_year) then ERROR_YEAR
>elsif (current_year >= data_year) then OKAY_YEAR
>
>if(OKAY_YEAR) then
> if(current_month < data_month) then ERROR_MONTH
> elseif(current_month >= data_month) then OKAY_MONTH
>
>if(OKAY_MONTH) then
> if(current_day < data_day) then ERROR_DAY
> elseif(current_day >= data_day) then OKAY_DAY
>
>if(OKAY_DAY) then
> if(current_hour < data_hour) then ERROR_HOUR
> elseif(current_hour >= data_hour) then OKAY_HOUR
>
>if(OKAY_HOUR) then
> ....
>
>Okay, I think you will understand what the alogrithm going to do, it
>isn't a good ways to compare, coz, I know some guru who use a good
>alogrithm to compare better then mind.
>
>Pleas help,
>Thank
>

I had the same issue, but tackled it using the MySQL functions
from_unixtime and unix_timestamp, but this won't work for all dates.
Converting times to MySQL/ISO format and comparing those also works.

Chad
--
Chad Hanna
IT Manager Berkshire Family History Society www.berksfhs.org.uk
Systems Developer FamilyHistoryOnline www.familyhistoryonline.net
FreeBSD Apache MySQL Perl mod_perl
 
Reply With Quote
 
phal
Guest
Posts: n/a
 
      09-27-2005
Dear friend

Actually, the problem not concern under MySQL, my main problem is how I
going to compare the Date-time after I had retrieved from MySQL
database. If the time in database is older then the current_time, the
program will print expire, and user cannot see the information. On the
other hand, it will keep print till the time expired. When the time
expired? Yes, during record the TIME and DURATION in database, the TIME
+ DURATION will recorded,

The following information will be easier for understand the process
if (TIME + DURATION > CURRENT_TIME) Then
keep printing
else if ( [ TIME + DURATION ] == CURRENT_TIME) then
stop printing and expired
else
print ERROR

The only issue here is the algorithm that I going to use for comparing
, I wish someone around can help me for this, coz, I try search many
web sites for the solution, it isn't what I want. Thank.

Phal

 
Reply With Quote
 
Gunnar Hjalmarsson
Guest
Posts: n/a
 
      09-27-2005
phal wrote:
> Actually, the problem not concern under MySQL, my main problem is how I
> going to compare the Date-time after I had retrieved from MySQL
> database.


So you are actually talking about any time string in the format
'YYYYMMDDHHMMSS'? You can use Time::Local to get epoch seconds:

use Time::Local;
my $time = '20050927120000';
my @t = substr $time, 0, 4, '';
push @t, substr $time, 0, 2, '' while $time;
my $epoch = timelocal @t[5,4,3,2], $t[1]-1, $t[0];

if ( $epoch + $duration > time() ) {
...

--
Gunnar Hjalmarsson
Email: http://www.gunnar.cc/cgi-bin/contact.pl
 
Reply With Quote
 
Mothra
Guest
Posts: n/a
 
      09-27-2005
Hello Phal,

phal wrote:
> Dear friend
>
> Actually, the problem not concern under MySQL, my main problem is how
> I going to compare the Date-time after I had retrieved from MySQL
> database. If the time in database is older then the current_time, the
> program will print expire, and user cannot see the information. On the
> other hand, it will keep print till the time expired. When the time
> expired? Yes, during record the TIME and DURATION in database, the
> TIME + DURATION will recorded,
>

This may work for you

use strict;
use warnings;
use DateTime;
use DateTime::Format::Mysql;
use DateTime:uration;

my $today = DateTime->now();
my $d = DateTime:uration->new(
days => '4',
hours => '5',
);

while (<DATA>) {
my $dt = DateTime::Format::MySQL->parse_datetime($_);
if ( $today + $d > $dt ) {
print "Today plus the duration is greater than\n";
}
elsif ( $today + $d < $dt ) {
print "Today plus the duration is less than\n";
}
elsif ( $today + $d == $dt ) {
print " Today plus the duration is equal\n";
}

}
__DATA__
2003-01-16 23:12:01
2004-05-06 11:15:55
2005-10-16 15:12:00

Hope this helps

Mothra


 
Reply With Quote
 
Anno Siegel
Guest
Posts: n/a
 
      09-28-2005
Gunnar Hjalmarsson <(E-Mail Removed)> wrote in comp.lang.perl.misc:
> phal wrote:
> > Actually, the problem not concern under MySQL, my main problem is how I
> > going to compare the Date-time after I had retrieved from MySQL
> > database.

>
> So you are actually talking about any time string in the format
> 'YYYYMMDDHHMMSS'? You can use Time::Local to get epoch seconds:
>
> use Time::Local;
> my $time = '20050927120000';
> my @t = substr $time, 0, 4, '';
> push @t, substr $time, 0, 2, '' while $time;


This (fixed format strings) is a place where unpack() shines -- shorter
and non-destructive:

@t = unpack 'a4 (a2)*', $time;

The (relatively new) possibility of parenthesizing partial templates,
as in "(a2)*" is a major win here. Before that one would have had
to spell out the template "a4 a2 a2 a2 a2 a2". That is not only more
typing, it commits the code to expecting exactly that many "a2" elements.

Anno
--
If you want to post a followup via groups.google.com, don't use
the broken "Reply" link at the bottom of the article. Click on
"show options" at the top of the article, then click on the
"Reply" at the bottom of the article headers.
 
Reply With Quote
 
Gunnar Hjalmarsson
Guest
Posts: n/a
 
      09-28-2005
Anno Siegel wrote:
> Gunnar Hjalmarsson wrote:
>>
>> use Time::Local;
>> my $time = '20050927120000';
>> my @t = substr $time, 0, 4, '';
>> push @t, substr $time, 0, 2, '' while $time;

>
> This (fixed format strings) is a place where unpack() shines -- shorter
> and non-destructive:
>
> @t = unpack 'a4 (a2)*', $time;


Thanks for mentioning that, Anno.

After I had posted, it struck me that

my @t = substr($time, 2) =~ /../g;

would have been another option, but since that involves the regex
engine, I presume unpack() is better.

--
Gunnar Hjalmarsson
Email: http://www.gunnar.cc/cgi-bin/contact.pl
 
Reply With Quote
 
 
 
Reply

Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Using Python and Connecting to MySQL remotely WITHOUT MySQL installedon local computer dave Python 4 11-18-2010 04:19 AM
Re: How to insert PDF file in to MySql and read it from MySql toJAVA App Jeffrey H. Coffield Java 1 07-24-2009 12:29 AM
Re: How to insert PDF file in to MySql and read it from MySql to JAVA App Roedy Green Java 0 07-23-2009 05:30 PM
mySQL Ruby Gem and MAMP mySQL Mark Meijer Ruby 3 02-03-2008 04:19 AM
Has Anyone build A Mysql Module for ruby1.8.2 and Mysql 4.1.8 bin liu Ruby 1 12-22-2004 03:39 AM



Advertisments