Perl time and Mysql time

Discussion in 'Perl Misc' started by phal, Sep 27, 2005.

  1. phal

    phal Guest

    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
    phal, Sep 27, 2005
    #1
    1. Advertising

  2. phal

    Hal Vaughan Guest

    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
    Hal Vaughan, Sep 27, 2005
    #2
    1. Advertising

  3. phal

    phal Guest

    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
    phal, Sep 27, 2005
    #3
  4. phal

    Brian Wakem Guest

    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
    Brian Wakem, Sep 27, 2005
    #4
  5. phal

    Chad Hanna Guest

    In message <>, phal
    <> 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
    Chad Hanna, Sep 27, 2005
    #5
  6. phal

    phal Guest

    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
    phal, Sep 27, 2005
    #6
  7. 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
    Gunnar Hjalmarsson, Sep 27, 2005
    #7
  8. phal

    Mothra Guest

    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::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
    Mothra, Sep 27, 2005
    #8
  9. phal

    Anno Siegel Guest

    Gunnar Hjalmarsson <> 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.
    Anno Siegel, Sep 28, 2005
    #9
  10. 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
    Gunnar Hjalmarsson, Sep 28, 2005
    #10
  11. phal

    Anno Siegel Guest

    Gunnar Hjalmarsson <> wrote in comp.lang.perl.misc:
    > 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.


    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
    --
    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.
    Anno Siegel, Sep 28, 2005
    #11
  12. phal

    phal Guest

    Thank everyone, I can cope with the problem,
    phal, Sep 30, 2005
    #12
  13. phal

    phal Guest

    Thank everyone, I can cope with the problem,
    phal, Sep 30, 2005
    #13
  14. phal

    phal Guest

    Thank everyone, I can cope with the problem,
    phal, Sep 30, 2005
    #14
    1. Advertising

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

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Jeffrey H. Coffield
    Replies:
    1
    Views:
    1,823
  2. Roedy Green
    Replies:
    0
    Views:
    1,459
    Roedy Green
    Jul 23, 2009
  3. dave
    Replies:
    4
    Views:
    1,350
    Steve Holden
    Nov 18, 2010
  4. bin liu
    Replies:
    1
    Views:
    122
    Curt Hibbs
    Dec 22, 2004
  5. Mark Meijer

    mySQL Ruby Gem and MAMP mySQL

    Mark Meijer, Feb 2, 2008, in forum: Ruby
    Replies:
    3
    Views:
    159
    louis
    Feb 3, 2008
Loading...

Share This Page