Perl time and Mysql time

P

phal

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
 
H

Hal Vaughan

phal said:
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
 
P

phal

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
 
B

Brian Wakem

phal said:
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.
 
C

Chad Hanna

phal said:
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
 
P

phal

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
 
G

Gunnar Hjalmarsson

phal said:
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() ) {
...
 
M

Mothra

Hello Phal,
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::Duration;

my $today = DateTime->now();
my $d = DateTime::Duration->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
 
A

Anno Siegel

Gunnar Hjalmarsson said:
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
 
G

Gunnar Hjalmarsson

Anno said:
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.
 
A

Anno Siegel

Gunnar Hjalmarsson said:
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.

As always, what is better depends on your goals. I'd expect unpack to be
fastest, though there have been surprises. A regex, if it's simple like
here, has the advantage of being immediately comprehensible (to those who
read regex). In that case I'd go whole hog and do

my @t = $time =~ /(....)(..)(..)(..)(..)(..)/;

Can't get clearer than that.

To say something in favor of the substr solution, it is elementary. You
can apply the method more or less unchanged in all languages that support
basic string manipulation.

Anno
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Members online

No members online now.

Forum statistics

Threads
473,744
Messages
2,569,484
Members
44,904
Latest member
HealthyVisionsCBDPrice

Latest Threads

Top