SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    Confirmed Halfwit
    Join Date
    Oct 1999
    Location
    Vancouver, BC, Canada
    Posts
    983
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    I'm stuck on db related "time period" issues..

    Phew! Well, I've been thinking about this problem for way too long now and I've totally lost my ability to think clearly. I'm hoping that someone can push me in the right direction..

    I'm trying to design a table that I can use to record unique "Start Time" and "Stop Time" values so I can use them for validation and comparison later on. The "Time" in this case should contain a date and hour/minute value.

    Should I have columns like this (it will likely be a mySQL database):

    Code:
    CodeID	StartDate		StartTime		StopDate		StopTime
    
    001	2001-09-11	11:00		2001-09-13	00:00
    002	2001-09-13	00:00		2001-09-14	00:00
    003	2001-09-14	00:00		2001-09-15	00:00
    004	2001-09-15	00:00		2004-01-01	00:00
    Or columns like this:

    Code:
    CodeID	StartTimestamp	StopTimestamp
    
    001	2001-09-11 11:00	2001-09-13 00:00
    002	2001-09-13 00:00	2001-09-14 00:00
    003	2001-09-14 00:00	2001-09-15 00:00
    004	2001-09-15 00:00	2004-01-01 00:00
    And now.. taking this a step further.. here's what I want to do with this table:

    1. I need some validation code for my edit form, so that I can return a message to the user if they try to enter a "Start Time" or "Stop Time" that conflicts with existing time periods in the table.

    For example, if the above data were real, the user should not be allowed to enter a new record where the "Start Time" is 2001-09-16 00:00 and "Stop Time" is 2001-09-24 00:00. The desired time period would conflict with the 4th record since the 4th record will not stop until 2004.

    The user would receive a message stating that "CodeID 004 is already active during the desired time." Then they would have to edit the "Stop Time" for the CodeID 004 record to something less than the desired "Stop Time" for the new record. After that, they would try to resubmit the new record.

    Does that make sense? Anyone have any existing code snippets that could help?

    2. Let's say today is 2001-09-13 and it is now 14:00 hours. I need some SQL code that can pick the right value out of the table. So using the data above, the CodeID 002 record would be selected.

    Code:
    SELECT CodeID FROM myTable WHERE ...erk ... I guess this depends on what table scheme I use..
    Does anyone know how to do any of this?
    Last edited by hstraf; Sep 20, 2001 at 20:40.
    - A simple online WYSIWYG editor for HTML code snippets.
    - Managed Web Hosting - $3.95/month (resellers welcome)
    - Why pay more? $8.95 domains & $9.95 SSL certificates!

  2. #2
    We like music. weirdbeardmt's Avatar
    Join Date
    May 2001
    Location
    Channel Islands Girth: Footlong
    Posts
    5,882
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    if your stopdateandtime must be exactly equal to a date and time combination then use the second one.

    Im intrigued.. what do you want to use this for?

    and you could use something like:

    select * from thetable where stoptimedate = "$stoptimedate";

    or something...
    I swear to drunk I'm not God.
    Matt's debating is not a crime
    Hint: Don't buy a stupid dwarf Clicky

  3. #3
    Confirmed Halfwit
    Join Date
    Oct 1999
    Location
    Vancouver, BC, Canada
    Posts
    983
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    It's for a "code snippet" content management system. The idea is that people in the company will be able to manage their own content, but they must have the ability to put it into a "timeline". So they are supposed to be able to specify that they want such-and-such code to be active only from time period 1 to time period 2.

    Obviously, they shouldn't be able to submit "code snippets" that would conflict with existing snippets.

    Anyone else have a thought on how to do these things?
    - A simple online WYSIWYG editor for HTML code snippets.
    - Managed Web Hosting - $3.95/month (resellers welcome)
    - Why pay more? $8.95 domains & $9.95 SSL certificates!


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •