oracle time format query

Oracle is a popular relational database management system that is widely used in various enterprise-level applications. In Oracle, date and time are very important data types because the implementation of many business functions depends on them. Oracle supports multiple date and time formats. This article will introduce how to query and use date and time formats in Oracle.

1. Oracle date and time data types

Oracle supports 4 date and time data types, namely DATE, TIMESTAMP, TIMESTAMP WITH TIME ZONE and TIMESTAMP WITH LOCAL TIME ZONE.

1. DATE: The most commonly used date and time data type, it can store date and time, including year, month, day, hour, minute and second, and supports operations, comparisons and formatting.
2. TIMESTAMP: A more precise date and time data type than DATE, supporting nanosecond precision.
3. TIMESTAMP WITH TIME ZONE: Time zone information is added based on TIMESTAMP.
4. TIMESTAMP WITH LOCAL TIME ZONE: Similar to TIMESTAMP WITH TIME ZONE, but it converts the time to local time in the database time zone.

2. Oracle date and time format query

When querying date and time data, we need to use the TO_CHAR function to convert the date and time into a specific format. The following are some commonly used Oracle date and time formats:

Date format:

• YYYY: Year, four digits (for example: 2021)
• YY: Year , represented by two digits (for example: 21)
• MM: month, represented by digits (for example: 09)
• MON: month, represented by abbreviation (for example: SEP)
• MONTH : month, expressed in full name (for example: SEPTEMBER)
• DD: day, expressed in digits (for example: 08)
• DY or DAY: day of the week, expressed in abbreviation or full name (for example: MON or MONDAY )

Time format:

• HH24: hour, 24-hour format, digital representation
• MI: minute, digital representation
• SS: seconds, numerical representation

Example:

1. Query the current date and time: SELECT TO_CHAR(SYSDATE,'YYYY-MM-DD HH24:MI:SS') FROM DUAL;
2. Query the current date: SELECT TO_CHAR(SYSDATE,'YYYY-MM-DD') FROM DUAL;
3. Query the current time: SELECT TO_CHAR(SYSDATE,'HH24:MI:SS' ) FROM DUAL;
4. Query the date one week ago: SELECT TO_CHAR(SYSDATE-7,'YYYY-MM-DD') FROM DUAL;
5. Query the current month: SELECT TO_CHAR(SYSDATE,' MM') FROM DUAL;
6. Query the current quarter: SELECT TO_CHAR(ADD_MONTHS(TRUNC(SYSDATE,'Q'),2),'Q') FROM DUAL;
7. Query the current year: SELECT TO_CHAR(SYSDATE,'YYYY') FROM DUAL;
8. Query tomorrow's date: SELECT TO_CHAR(SYSDATE 1,'YYYY-MM-DD') FROM DUAL;
9. Query the date of this month The date of the day: SELECT TO_CHAR(TRUNC(SYSDATE,'MONTH'),'YYYY-MM-DD') FROM DUAL;
10. Query the date of the last day of the previous month: SELECT TO_CHAR(LAST_DAY(ADD_MONTHS(SYSDATE ,-1)),'YYYY-MM-DD') FROM DUAL;

3. Use of date and time functions

In addition to the TO_CHAR function, Oracle also provides many Date and time functions can be used when querying and processing date and time data.

1. ADD_MONTHS(date,n): Add n months to the date. n can be a negative number to subtract n months.
2. MONTHS_BETWEEN(date1,date2): Calculate the number of months difference between date1 and date2.
3. TRUNC(date, format): Truncate the date according to the specified format, often used to calculate date intervals and comparisons.
4. LAST_DAY(date): Returns the last day of the month in which the date is located.
5. EXTRACT(unit FROM date): Extract the specified time unit (year, month, day, hour, minute, second, day of the week, etc.) from the date.

Example:

1. Query how long it has been since the employee’s joining date: SELECT MONTHS_BETWEEN(SYSDATE,hire_date)/12 FROM employees WHERE employee_id=100;
2. Query employees whose birthdays are in this month: SELECT * FROM employees WHERE EXTRACT(MONTH FROM birth_date) = EXTRACT(MONTH FROM SYSDATE);
3. Query how many days are left until the employee’s next birthday: SELECT TRUNC( MONTHS_BETWEEN(TRUNC(SYSDATE,'YYYY'),TRUNC(birth_date,'YYYY'))/12)365 TRUNC(MOD(MONTHS_BETWEEN(TRUNC(SYSDATE,'YYYY'),TRUNC(birth_date,'YYYY') ),1)12.0/11*365) FROM employees WHERE employee_id=100;

The above are just some commonly used examples of date and time queries and functions. Actual use must be based on specific Flexible use and combination according to needs.

Summary:

Oracle date and time format query is an indispensable part of database development. The correct date and time format and calculation method can ensure the accuracy and stability of business data. This article introduces Oracle's data types, date and time formats and functions in detail. I hope it will be helpful to everyone's date and time query and processing in practice.

The above is the detailed content of oracle time format query. For more information, please follow other related articles on the PHP Chinese website!