SitePoint Sponsor

User Tag List

Results 1 to 7 of 7

Thread: Time()

  1. #1
    SitePoint Evangelist priti's Avatar
    Join Date
    Aug 2006
    Location
    India
    Posts
    488
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Time()

    Hi,

    I have following record

    day starttime endtime
    sun 08:00:00 12:00:00

    now i want to check 09:00:00 is shop is open??

    so how am i suppose to do that i tried it with between and like dates but it doesn't work as expected

    for 09:00:00 it sud show me the above record....it is not!!

    i know this is simple but i am very confuse as of now.

  2. #2
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2008
    Posts
    5,757
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    What did you try?

    between should work, although you might want to use '09:00:00' >= starttime and '09:00:00' < endtime

  3. #3
    SitePoint Evangelist priti's Avatar
    Join Date
    Aug 2006
    Location
    India
    Posts
    488
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Is following query correct with the view of performance!! is it a good practice !!

    SELECT * FROM table WHERE day='Friday' AND '2010-01-29' BETWEEN start_date and end_date

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,322
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    could you do a SHOW CREATE TABLE please

    i want to see this column called "day" as well as how your starttime and endtime columns are defined

    '2010-01-29' BETWEEN start_date and end_date is okay, except you don't have columns like that, do you?

    another reason to display your SHOW CREATE TABLE
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Evangelist priti's Avatar
    Join Date
    Aug 2006
    Location
    India
    Posts
    488
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    CREATE TABLE `individual_availability` (
    `aid` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
    `day` enum('sunday','monday','tuesday','wednesday','thursday','friday','saturday') NOT NULL,
    `start_date` date DEFAULT NULL,
    `end_date` date DEFAULT NULL,
    `start_time` time DEFAULT NULL,
    `end_time` time DEFAULT NULL,
    PRIMARY KEY (`aid`)
    ) ENGINE=InnoDB AUTO_INCREMENT=32 DEFAULT CHARSET=latin1

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,322
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    okay, let's use the data you provided in post #1 and a couple of additional rows...
    Code:
    insert into individual_availability 
    ( day, start_date, end_date, start_time, end_time)
    values
     ('sunday','2010-02-01','2010-02-28','08:00:00','12:00:00')
    ,('monday','2010-02-01','2010-02-28','08:00:00','08:30:00')
    ,('friday','2010-02-01','2010-02-28','08:00:00','23:00:00')
    ;
    now let's try your query -- "now i want to check 09:00:00 is shop is open??"
    Code:
    SELECT *
      FROM individual_availability
     WHERE '09:00:00' BETWEEN start_time AND end_time
    
    aid  day  start_date  end_date  start_time  end_time
    32  sunday  2010-02-01  2010-02-28  08:00:00  12:00:00
    34  friday  2010-02-01  2010-02-28  08:00:00  23:00:00
    i think this is correct, what do you think?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Evangelist priti's Avatar
    Join Date
    Aug 2006
    Location
    India
    Posts
    488
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    yes i think it's right ..


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
  •