Say you have a table `USER` as below
If you want to do group by the SELECT query using the date field for each day then it would consider the time with the date while grouping. For example the below query.
Output will be
To get the exact result for each day by eliminating the time you would need to truncate the time from the date field. You can use the trunc function of oracle to remove time part.
Output will be
This same feature is also available in later version of mysql. But I don't know what is the exact function and way to use.
ID, JOIN_DATE, STATUS
00,'13-Sep-2006 08:47:54 AM', 0
01,'13-Sep-2006 08:48:54 AM', 0
02,'13-Sep-2006 08:49:54 AM', 0
03,'13-Sep-2006 08:50:54 AM', 0
04,'14-Sep-2006 08:54:54 AM', 0
10,'14-Sep-2006 08:59:54 AM', 0
12,'14-Sep-2006 09:00:54 AM', 0
If you want to do group by the SELECT query using the date field for each day then it would consider the time with the date while grouping. For example the below query.
select JOIN_DATE, count(*) from USER where STATUS = 0 group by JOIN_DATE;
Output will be
13-Sep-2006 08:47:54 AM, 1
13-Sep-2006 08:48:54 AM, 1
13-Sep-2006 08:49:54 AM, 1
13-Sep-2006 08:50:54 AM, 1
14-Sep-2006 08:54:54 AM, 1
14-Sep-2006 08:59:54 AM, 1
14-Sep-2006 09:00:54 AM, 1
To get the exact result for each day by eliminating the time you would need to truncate the time from the date field. You can use the trunc function of oracle to remove time part.
select trunc(JOIN_DATE), count(*) from USER where STATUS = 0 group by trunc(JOIN_DATE);
Output will be
13-Sep-2006, 4
14-Sep-2006, 3
This same feature is also available in later version of mysql. But I don't know what is the exact function and way to use.
Comments