in reply to Re: Multiple date ranges
in thread Multiple date ranges

I knew I should have qualified the statement about the pipe-delimited file. This is one of those "*I* wouldn't have done it this way, but I've inherited this system" problems.

In the long-term, I indeed do intend to move it to a DBI/DBD based system, but there are all sorts of other details I won't bore you with that are going to make that a timescale of months, not days. Meanwhile, I still need to solve this in a non-SQL way using what I have: hashes made by reading the pipe-delimited file ...

A.

Replies are listed 'Best First'.
Re: Re: Re: Multiple date ranges
by Anonymous Monk on Feb 03, 2003 at 13:17 UTC
    What format are the timestamps in?

    Convert the timestamps to seconds (use any means neccessary).

    Create a datastructure resembling 

    $foo{'bar'} = [
   1, # first joined
   2, # left
   3, # joined again
   4, # left
   5, # joined again
];

    [download]
    If a user has an odd number of timestamps, he's still a member.

    It's fairly obviouls how to go about querying for other values.

    If the code is clean, switching to DBI needn't take more than a few days (i got a feeling its not though *shudder*).

[reply]
[d/l]

      The timestamps were entered as free text in a web form. No checking of any kind - pick a format, any format. I have a roughly 200 line perl regexp script that has got (nearly)all of them into ISO format (YYYY-MM-DD).

      Spotting who is currently a member is easy (apart from anything else, that info exists in another database I didn't tell you about). What I was looking for was a more elegant way of handling the other queries, e.g. using Date::Range, than a brute force approach...

[reply]
        The timestamps were entered as free text in a web form.No checking of any kind - pick a format, any format. I have a roughly 200 line perl regexp script that has got (nearly)all of them into ISO format (YYYY-MM-DD).
        Did you give Date::Manip a try?? It's a big and slow module but quite good at parsing dates in any format, take a look at the ParseDate function. This could save you from having to program monsters like 200 line regex scripts ...

        -- Hofmator

[reply]
        You know what, use DBD-SQLite.

        Date::Range is not for you (unless you got tons of memory, and wanna have 4500 Date::Range objects --- stupid)

        Build a new database out of your flat file, and SQL to query it any which way you want.

[reply]
        The timestamps were entered as free text in a web form.
        I hope that's the #1 thing on your to-do list (give the users a pulldown menu, or simply use localtime/gmtime)

        You wanna handle SQL-esque type queries in a non-SQL way, you're gonna have to bend over backwards to do it.

        You need to involve another database and create kinds of indexes.

[reply]
Re: Re: Re: Multiple date ranges
by Anonymous Monk on Feb 03, 2003 at 14:39 UTC
    You don't need to solve this in a non-SQL way.

    Take this opportunity to port your database to SQL (build it out of the pipe file).

    Then run SQL queries (which is what you're trying to do), so by the time your frontend catches up (the other code), you already have a nice database/search backend all ready.

    Using CGI::Application the re-development of your frontend can take lots and lots and lots less time.

    Using DBD::SQLite can save you the setup of a real database.

[reply]

      I looked at CGI::Application and am looking again, as there is an updated version just out, but I am trying to solve the (many) information problems first, before I set about improving the CGI ...

      I also looked at SQLite, but it's not clear whether it handles Unicode properly or not. The docs don't mention it; the only reference is in the changes file, where it says "fixed Unicode tests".

      Another problem I have is that some of the data is in XML format and I'm sure you know that mapping XML onto the RDBMS model can be a sure-fire headache generator.

[reply]
Re: Re: Re: Multiple date ranges
by Anonymous Monk on Feb 03, 2003 at 13:25 UTC
    use DB_File; with the $DB_HASH and allow keys to have multiple values.

    Make the the keys the date stamps, and the values user names.

    Now you can do partial matches, and can have a many-to-one relationship (on 2003-02-01 , 10 users joined, 4 left)

    Since you can have multiple values, you can make the values contain if a user left or joined (pipe it to me), or you can have 2 hashes (%joined, %left), or simply prefix the date strings (left-2003-02-01 or joined-2002-09-22) so you can keep a single %hash.

    I'd do it this way, cause I love DB_File.

[reply]
[d/l]
      I mean one-to-many ;)

      DB_File is fast.

[reply]
Re: Re: Re: Multiple date ranges
by tall_man (Parson) on Feb 03, 2003 at 15:43 UTC
    You don't need to solve this with SQL if you don't want to. In perl, TMTOWTDI. I would suggest you look at the module Date::Set. You should be able to add to the sets like this: 
    $a->event( start => '19971024T120000Z',
           end   => '19971025T120000Z' );  # one period
$a->event( start => '20001101T120000Z');   # open-ended

    [download]
    Simple set-inclusion queries will tell you who is active on a given date. SQL might be better in the long run, but something like this would get you going.
[reply]
[d/l]

      Thanks for this suggestion. Do you think it will scale OK with the 4500+ records and the fact the membership dates go back to 1949?

[reply]

      Just checked the info for Date::Set

      (no support for years outside the 1970-2038 range).

      So, that's no good for me, as I have dates going back to 1949 ...

[reply]

In Section Seekers of Perl Wisdom