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

what's long? mysql doesn't have that

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

so, what time is it when it is 35213556243????

the date will be 1971/02/12 for 35213556243

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?

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>;

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???

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.