Oracle date functions: difference between past, present, and date

We have finished sorting out the Oracle numeric functions in the last few days, and sorted out the Oracle date functions this evening. If you cannot remember all these things, you must be aware of them. Knowing that they exist can avoid a detour in the development process. From the end to the end, there are still a lot of GAINS.

I. Date Algorithm

1. sysdate, current_date, and systimestamp

Oracle uses the current date and time of the computer operating system through the sysdate function. The sysdate function can be used in any other place where the Oracle function can be used, it can be regarded as a hidden or pseudo column for each table.

Select sysdate from dual; Result: 19:14:21

Current_date returns the system date of the current session's time zone. We usually use UTC + 8. If you change the current session time to UTC + 9, try:

Alter session set time_zone = '+ 19:27:32'; select current_date, sysdate from dual result: 12/24

We can also see the difference between them through the example.

There is also a function called systimestapm, which returns the system time of the Local Machine (including microseconds and time zones). It has nothing to do with the time zone of the session.

Select distinct imestamp from dual result: 24-12-13 07.34.37.042000 PM +

2. Difference between two dates

Two dates can be directly added or subtracted. The returned value is in the unit of day. You can also use a date to add or subtract a value to represent N days (Before)

For example, if I pay after ten days, what is the day after ten days?

Select sysdate + 10 from dual result: 19:38:19

For example, if today's New Year's Eve is, how many days are there before the New Year's Eve?

Select to_date ('1970-01-30 ', 'yyyy-MM-dd')-sysdate from dual result: 2014

Dizzy. It will take more than a month to celebrate the Chinese New Year.

3. Add month and reduce month

Add_months is used.

Query which day is three months later and three months ago:

Select add_months (sysdate, 3), add_months (sysdate,-3) from dual result: 19: 47: 182013/9/22 19:47:18

You don't need to add or reduce the year. If the value is 12, you can determine which day the year is N years ago or N years later.

4. greatest and least

These two functions mentioned in the Introduction of numeric functions, which respectively represent the maximum and minimum values of a set of values.

The two are also applied in the date function. greatest is the closest date from a group of date data, and least is the earliest date selected in a group of dates.

Select greatest (to_date ('2017-11-11 ', 'yyyy-MM-dd'), to_date ('2017-12-11', 'yyyy-MM-dd '), to_date ('2017-01-05 ', 'yyyy-MM-dd'), to_date ('2017-11-11', 'yyyy-MM-dd') from dual result: 12013/1/5 select least (to_date ('1970-11-11 ', 'yyyy-MM-dd'), to_date ('1970-12-11', 'yyyy-MM-dd '), to_date ('2017-01-05 ', 'yyyy-MM-dd'), to_date ('2017-11-11', 'yyyy-MM-dd') from dual result: 12013/11/11

Note: to_date must be used to convert strings. Otherwise, they will not be able to judge whether they are in the date format and can only be processed as strings. Results are prone to errors.

5. next_day indicates the day starting from the specified date.

Select next_day (to_date ('1970-12-23 ', 'yyyy-MM-dd'), 2) from dual result: 2013

Note: here 2 represents Monday, not Tuesday. 1234567 represents Sunday and Monday.

6. What is the last day of a month calculated by last_day?

Select last_day (sysdate) from dual result: 2013/12/31 20: 46: 077, months_between calculates the number of months with a difference between the two dates. The returned results may contain decimal places.

Calculate your age:

Select floor (months_between (sysdate, to_date ('1970-08-24 ', 'yyyy-MM-dd')/12) from dual result: 1990, combined date function: use multiple date functions in combination

For example, if you start a new company in October 28, 2013 and have a two-month probation period, you can submit a positive report on the first day of each month after the new employee passes the probation period. When will the job be finalized in October 28:

Select last_day (add_months (to_date ('1970-10-28 ', 'yyyy-MM-dd'), 2) + 1 from dual result: 2013

Idea: first use add_months to calculate the day after two months, then use last_day to calculate the last day of the month, and then + 1 to represent the first day of the next month.

9. round and trunc in date calculation

The article on numeric functions also focuses on the round and trunc functions, which introduce their application in numerical functions. In fact, the two brothers in the date function have similar functions as numerical functions.

When we subtract a date from another date, it often carries decimals, because the hour, minute, and second are retained in Oracle.

Sometimes we don't need time, minute, or second, so we need to use the round and trunc functions.

The role of the round function is: if one day is before noon, the round function is used to set the time of the next day to 0 (midnight ), if it is set to the next day after noon, This is the rounding of time.

Role of the trunc function: remove it. No matter the time of the day, after the trunc function is used, the change time is set to of the day.

Select to_date ('1970-12-23 ', 'yyyy-MM-dd')-round (sysdate), to_date ('1970-12-23', 'yyyy-MM-dd ') -trunc (sysdate) from dual result: 0 1

Ii. Use to_date and to_char to set the date and time formats

1. Basic use

To_date and to_char functions have the opposite effect, but their usage is similar.

To_date is to convert the string to the time format to_char is to convert the date to the string format

We usually use too many of these two functions. Let's first write two examples and then talk about other functions:

Select to_date ('1970-11-11 12:01:14 ', 'yyyy-MM-dd HH: MI: ss') from dual result: 2013 12: 01: 14 select to_char (sysdate, 'yyyy-MM-dd HH: MI: ss') from dual; Result: 09:37:48

When to_char is used, you can insert a string in the format parameter in double quotation marks:

Select to_char (sysdate, 'yyyy "year" MM "month" dd "day" ') from dual; Result: February 1, December 22, 2013

2. The most common to_char Error

MM indicates that the MI Team of the month is not mixed in minutes. Since the time supported in Oracle is flexible, it won't prompt you if you get mixed up.

Select to_char (sysdate, 'yyyy-MI-dd hh: mm: ss') from dual result: 2013-45-22 09:12:15

It is a sad reminder that such an error occurs. Fortunately, it is easy to find the error within 45 minutes. If it is within 1-12, it is not easy to find the error from the result.

3. to_date not only accepts string parameters, but also accepts numeric parameters:

Select to_date (20131222, 'yyyy-MM-dd') from dual result:

4. new_time: Change the time zone

New_time (date, 'this', 'other ')

Three parameters: date indicates the time in this time zone, and 'eas' indicates that it is replaced by an abbreviation consisting of three letters, representing the current time zone, 'other' is also replaced by an abbreviation consisting of three letters, indicating other time zones.

For example, how long is the '2017-10-01 12:00:00 'in Beijing and Hawaii?

Select to_date ('2017-10-01 12:00:00 ', 'yyyy-MM-dd hh24: mi: ss'), new_time (to_date ('2017-10-01 12:00:00 ', 'yyyy-MM-dd hh24: mi: ss'), 'est ', 'hst') from dual result: 7:00:00/10/1

It should not be used frequently. Just understand it.

Iii. EXTRACT

EXTRACT can be used to replace the to_char function to select a part of the date value (for example, select a month and a day from a date)

Select extract (year from sysdate) from dual; select extract (month from sysdate) from dual; select extract (day from sysdate) from dual; Result: 20131222

Note: EXTRACT extracts the year, month, day, and extraction time parts differently.

Select extract (hour from timestamp '2017-12-22 22:34:12 ') from dual; select extract (minute from timestamp '2017-12-22 22:34:12') from dual; Result: 2013

In addition, if to_date is used to convert '2017-12-22 22:34:12 'to the date format, an error is returned.

What about the existence of this function? I haven't figured it out yet. I can use to_char to fully implement its functions.

Select to_char (sysdate, 'yyyy') from dual; select to_char (sysdate, 'hh ') from dual result: 201310

4. Use the TIMESTAMP type

DATE is accurate to the DATE and time of the second in data type storage, and the DATE of the second in the second TIMESTAMP data type storage is accurate to the DATE of one thousandth of the second.