The use of the Oracle-trunc function

Trunc function can be used to intercept date time

Usage: trunc (field name, precision)

Specific examples:

-- 按1分钟聚合 select trunc(stime,  ‘MI‘ )  as stime   -- 按1小时聚合 select trunc(stime,  ‘HH‘ )  as stime   -- 按1天聚合 select trunc(stime,  ‘DD‘ )  as stime   -- 示例 select trunc( cast ( ‘2017-11-09 17:42:57‘ as timestamp ),  ‘MI‘ )  as stime

Select Trunc (' 2017-11-09 17:42:57 ', ' MI ') as Stime Two query statement data as a result

--返回结果 2017-11-09 17:42:00 -- 按5分钟聚合 trunc(minutes_sub(stime,  minute (stime) % 5),  ‘MI‘ )   -- 按10分钟聚合 trunc(minutes_sub(stime,  minute (stime) % 10),  ‘MI‘ )   -- 示例 select trunc(minutes_sub( ‘2017-11-09 17:46:57‘ ,  minute ( ‘2017-11-09 17:46:57‘ ) % 5),  ‘MI‘ ) --返回结果 2017-11-09 17:45:00

In table table1, there is a field named Sysdate, the row id=123, date: 2016/10/28 15:11:58

1, the interception time to the year, the SQL statement is as follows:

Select Trunc (sysdate, 'yyyy') from table1 where id=123; --YYYY can also be replaced with year

Show: 2016/1/1

2, intercept the time to the month, the SQL statement:

Select Trunc (sysdate, 'mm') from table1 where id=123;

Show: 2016/10/1

3, intercept time to day, SQL statement:

Select Trunc (sysdate, 'dd') from table1 where id=123;

Show: 2016/10/28

4, intercept the time to the hour, the SQL statement:

Select Trunc (sysdate, 'hh') from table1 where id=123;

Display: 2016/10/28 15:00:00

5, intercept the time to the minute, the SQL statement:

Select Trunc (sysdate, 'mi') from table1 where id=123;

Display: 2016/10/28 15:11:00

6, interception time to the second temporarily do not know how to operate

7, not directly with Trunc (Sysdate, ' yyyy-mm-dd '), will prompt "precision specifier too much"

The use of the Oracle-trunc function