Results 1 to 7 of 7
  1. #1
    Join Date
    Apr 2011
    Posts
    3

    Unanswered: Retrieving records from yesterday night and today morning everyday

    Hi all,

    How to write a SQL Query to retrieve count of records each day(by getting sysdate from the system) for records inserted between yesterday night and today morning?

    pls. advise

    Thanks

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    providng you cna define what yesterday night and today morning is should be straightforward enough
    have a look at the MySQL date/time functions
    'all' you need to do is build the time banding appropriately
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Apr 2011
    Posts
    3

    Retrieving records from yesterday night and today morning everyday

    I just want a query like this

    select count(*) from table where stamp between (today @ 6 a.m) and (yesterday @ 8 p.m) and I want to use the sysdate for getting today's date as this task will run everyday at some particular time

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    seems reasonable enough
    so which verbs/functions/phrases do you think you need from the MySQL date/time functions
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Apr 2011
    Posts
    1
    The below query will display yesterday's date and today's date using current date.

    Use this in between the query u are using

    select date_ADD(date_sub(CURDATE(), INTERVAL 1 DAY), INTERVAL 20 HOUR), date_ADD(CURDATE(), INTERVAL 6 HOUR)

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    rvijayee, thanks (sarcasm intended) for spoiling healdem's attempt to get merryba to learn how to figure it out for himself

    welcome to dbforums and please do read the entire thread before jumping in again
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Apr 2011
    Posts
    3
    Thanks rvijayee

Posting Permissions

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