Results 1 to 9 of 9

Thread: query needed

  1. #1
    Join Date
    Mar 2008
    Posts
    7

    Unanswered: query needed

    hi,

    I have a db table that has one of the columns as the created_date with type long. I need the count of records that were created for each day(grouped by the date) for the last 10 days.

    Is it possible to frame a single query that will get me this result by passing the current date in long format to the query? I need a query that would probably run for oracle, mysql, ... as my db usage might change later on..

    Please help

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    what's long? mysql doesn't have that
    Code:
    select created_date, count(*) as howmany
      from daTable
     where created_date >= current_date - interval 10 day
    group by created_date
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Mar 2008
    Posts
    7
    to represent time im using bigint in mysql and number in oracle. Is it possible to make this query a generic one.. i mean something that would work for both oracle and mysql... I am even ok to pass the current date and the limit(10 days in milliseconds) as argument to the query
    Last edited by akhilachuthan; 03-12-08 at 02:35.

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    so, what time is it when it is 35213556243????
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Mar 2008
    Posts
    7
    the date will be 1971/02/12 for 35213556243

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    oh, that's a coincidence, i just typed a random string of digits, so i'm surprised it actually correlates to a real date

    what encoding scheme are you using for your bigints?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Mar 2008
    Posts
    7
    i am using java.. whenever a new entry has to be made to this db table, I get the system date as long and store that val as the 'createdtime' column entry. Now i have a requirement to get the number of records entered each day for day for the past few(say 5) days. The exact time is not specific.. i just want for the day.

    I was thinking for a result something like(if today is 12/3/08)

    date count
    === =====
    11/3 5
    10/3 4
    09/3 6
    08/3 2


    I have a query something like below, but this does not work for oracle .......
    select from_unixtime(time/1000,"%d - %m%m%m") as createddate, count(*) as mycount from <tablename>
    where <timecolumnname> > (unix_timestamp() - 864000 * 5) * 1000
    group by <timecolumnname>;

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    FROM_UNIXTIME doesn't work in Oracle? gee, that's too bad, you'll have to write a user-defined function, then

    by the way, FROM_UNIXTIME(35213556243/1000) is not 1971/02/12, it's 1970/02/10

    see how much trouble you're getting yourself into?

    what's wrong with using the international date standard which all database support???
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Feb 2005
    Location
    Utah
    Posts
    73
    your code is probably going to be more portable if you use the date classes in Java, assuming they have some, and the date or datetime datatypes in the databases. Not only will you be portable from an OS standpoint, but you will also get to use all of the functions that databases have implemented for date manipulation.
    Dandy
    Aspiring Database Dwarf

Posting Permissions

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