Oracle time calculation (date truncation and rounding)

Oracle time operations (date truncation and rounding) are similar to rounding numbers and bitwise truncation. Oracle also provides these two functions for time and date. But it is more complicated than rounding and intercepting numbers: this is because the time and date are formatted. Let's take a look at the definitions and usage of these two functions: the ROUND (date [, format]) TRUNC (date [, format]) Round Function rounds the date ", the Trunc function truncates a date. If the format is not specified, Round returns a date closest to the date parameter, while the Trunc function simply intercepts the hour, minute, and second sections and returns the year, month, and day sections. 2. Example of the Round and Trunc functions: SQL> select to_char (sysdate, 'yyyy-mm-dd hh24: mi: ss') now_date, 2 to_char (Round (sysdate ), 'yyyy-mm-dd hh24: mi: ss') round_date, 3 to_char (Trunc (sysdate), 'yyyy-mm-dd hh24: mi: ss ') trunc_date 4 from dual; NOW_DATE ROUND_DATE TRUNC_DATE hour ---------------------------------------- -------------------- 2008-06-30 14:52:13 2008-07-01 00:00:00 2008-06-30 0 0:00:00 is a typical example. Since the format of the round and trunc functions is not specified, Oracle uses the date and time format by default. In this example, the current time is PM, it has exceeded the middle line at AM, so Round returns day 07-01 instead of day 0-30. However, Trunc directly intercepts the previous date and returns the result regardless of the date. It is worth noting that the time and second returned by the two functions are both 00:00:00, that is, the start time of the day (for the 12-hour system, the return time is 12:00:00 AM ). 3. Example of Round and Trunc functions in the specified format: If we specify the format for the Round function and the Trunc function, the process becomes a bit complicated, but the core idea remains unchanged: Round is rounded down, trunc is a Trunc. For example, if we use the year format, the current Oracle judgment is based on the year, more than half of the year (that is, June 30), and the Round function returns the next year, the Trunc function returns the current year. SQL> select sysdate "Now date", 2 Round (sysdate, 'yyyy') Round_year, 3 Trunc (sysdate, 'yyyy') Trunc_year 4 from dual; now date ROUND_YEAR TRUNC_YEAR ---------- 30-6 months-08 01-1 month-08 01-1 month-08 there are many formats available for these two functions, however, this is basically the case when many applications are used. The following example uses the Round function: select Round (sysdate, 'q') Rnd_Q, Round (sysdate, 'month') Rnd_Month, round (sysdate, 'ww ') Rnd_Week, Round (sysdate, 'w') Rnd_Week_again, Round (sysdate, 'ddd') Rnd_day, Round (sysdate, 'dd') Rnd_day_again, round (sysdate, 'day') Rnd_day_of_week, Round (sysdate, 'D') Round, Round (sysdate, 'hh12') Rnd_hour_12, Round (sysdate, 'hh24') Rnd_hour_24, round (sysdate, 'mi') Rnd_minute from dual