Beefy Boxes and Bandwidth Generously Provided by pair Networks
"be consistent"
 
PerlMonks  

Date/Time Parse with time exemptions and SQL querying

by guice (Scribe)
on Sep 02, 2005 at 15:42 UTC ( [id://488688]=perlquestion: print w/replies, xml ) Need Help??

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

I have a programming delima that I'm not sure how to approach this.

I have a SQL table with a number of number with application name and epoch time/date stored for each one. And I have an excel file saying "for X apps I need data for M-F 7am-8pm, Sat 8am-6pm, Sun 9am-1pm", etc. The times and dates can very with each application.

I'm trying to figure out what would be the best way to tackle this problem:
1 - Storing of the application and time range values (retain simple human editability).
2 - How to gather such data (although, I suspect I can write some ugly SQL statement).

Anybody have thoughts on how to approach this?

Thanks

Updated: Updated title. This isn't an excel thing; ingore the Excel peice. I'm not parsing an Excel document. They will be editing the date/time brackets per the format I give them.

-- philip
We put the 'K' in kwality!

Replies are listed 'Best First'.
Re: Date/Time Parse with time exemptions and SQL querying
by jhourcle (Prior) on Sep 02, 2005 at 18:16 UTC

    I think you have two real issues here -- how to store the data in the back end, and how to present it to the user so it's editable.

    It's really going to be a function of what your user interface is -- you mentioned Excel, but then said it wasn't, and you also mentioned it's in a SQL table. I would assume that you'd use a 'date' field, so it's just a matter of dealing with the rest of it.

    I'd treat the time ranges as reoccuring events, and look into how various other databases handle these things (Palm's storage of weekly occuring events, iCalendar specification, etc.), so that I'd be likely to be compatable ... how you present that to the user, I have no idea, without knowing your user interface.

    As for gathering data, I assume you just want to query for time that matches a range -- I'm not sure if all date functions are consistent across databases, but the general logic of 'M-F 7am-8pm' is:

    SELECT * FROM tablename WHERE appid = 'application id' AND DAYOFWEEK(datefield) IN ( 2,3,4,5,6 ) AND HOUR(datefield) BETWEEN 7 and 19

    The above assumes that items exactly at 8pm aren't of interest, and that 'BETWEEN' in your database includes the endpoints. (ie, 7 is 'between' 7 to 19). If you want to deal with minutes or include the end time , you can't use the 'between' trick, and will have to compare if the hours and minutes of the day fall in the right period, which you can simplify by using fractional hours:

    SELECT * FROM tablename WHERE appid = 'application id' AND DAYOFWEEK(datefield) IN ( 2,3,4,5,6 ) AND (HOUR(datefield)+MINUTE(datefield)/60) BETWEEN 7 and 20

    The logic should be good, but you might need to hammer out syntax issues for your particular DB (the date processing functions (name, how it handles days of the week), and if between is inclusive or exclusive)

      That's actually helpful. Forgot about using the MySQL functions to narrow down the date/time like that. As for your question:
      It's really going to be a function of what your user interface is -- you mentioned Excel, but then said it wasn't, and you also mentioned it's in a SQL table. I would assume that you'd use a 'date' field, so it's just a matter of dealing with the rest of it.
      The input date/time formats will be decided by myself. The excel is just where I have the initial list of times. I do apologize for mentioning it. The problem with the Excel file is 1; parsing it. And 2; data's not guarentee concistant. So to get around this limitation, I'm creating my own data storeage format for this. I'm debating on storing a time with each day of the week or doing some M-F type storage and parsing that out. All one line? Multiple lines, etc...

      -- philip
      We put the 'K' in kwality!

Re: Date/Time Parse with time exemptions and SQL querying
by McDarren (Abbot) on Sep 02, 2005 at 17:10 UTC

    Well, I don't know about anybody else but I can't for the life of me work out exactly what your question is.

    Perhaps if you asked some specific questions you may get a better response.

      How about some examples then ..

      Database contains rows of data with epoch time stamps

      Application one, listed M-F, 7am-9pm. Sat 8-6pm
      Application two, listed M-Sun 8am-7pm.

      I'm trying to figure out the best approach for storing the 'M-F' time schedules within a flat file that the script will read. Then I need to figure out the best way to parse that file and create an SQL query that will grab all the data only within the specified time schedules on an application per application basis.

      follow?

      -- philip
      We put the 'K' in kwality!

Re: Date/Time Parse with time exemptions and SQL querying
by QM (Parson) on Sep 02, 2005 at 16:09 UTC
      Alright, I've updated the title. It's a big longer, but more discriptive. This isn't related to Excel at all.

      -- philip
      We put the 'K' in kwality!

Log In?
Username:
Password:

What's my password?
Create A New User
Domain Nodelet?
Node Status?
node history
Node Type: perlquestion [id://488688]
Approved by ww
help
Chatterbox?
and the web crawler heard nothing...

How do I use this?Last hourOther CB clients
Other Users?
Others having an uproarious good time at the Monastery: (2)
As of 2024-03-15 05:20 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    No recent polls found