How to convert timestamp in oracle? Method introduction

Oracle timestamp conversion method

Oracle is a powerful database management system, and its timestamp type is a high-precision date and time data type. There are two types of timestamps: with time zone and without time zone. Timestamp types are stored in the database as 8 bytes (with time zone) or 7 bytes (without time zone) and can be stored to nanosecond level precision. In Oracle, the conversion of timestamp types is very simple. This article will introduce the method of Oracle timestamp conversion.

1. Conversion between Oracle timestamp and date type

Oracle timestamp type can be converted to date type, and date type can convert string into date through to_date function, and The timestamp type can convert a string into a timestamp through the to_timestamp function. The specific conversion method is as follows:

-- Convert timestamp to date
select to_date('2021-10-21 12:00:00', 'YYYY-MM-DD HH24:MI:SS') from dual;
-- Convert date to timestamp
select to_timestamp('2021-10-21 12:00:00', 'YYYY-MM-DD HH24:MI:SS') from dual;

When converting timestamp and date types, you need to specify the format of the date string. In the above example, the format of the date string is 'YYYY-MM-DD HH24:MI:SS', where YYYY represents the 4-digit year, MM represents the 2-digit month, DD represents the 2-digit date, and HH24 represents the 24-hour format The number of hours, MI represents the number of minutes, and SS represents the number of seconds.

2. Conversion between Oracle timestamp and timestamp with time zone

Oracle timestamp type can be converted to timestamp with time zone type, timestamp with time zone type can be converted with time zone Convert a date string to a timestamp type with time zone. The specific conversion method is as follows:

-- Timestamp to timestamp with time zone
select cast(to_timestamp('2021-10-21 12:00:00.123456789', 'YYYY-MM-DD HH24: MI:SS.FF9') as timestamp with time zone) from dual;
-- timestamp with time zone to timestamp
select cast(from_tz(to_timestamp('2021-10-21 12:00:00.123456789' , 'YYYY-MM-DD HH24:MI:SS.FF9'), 'Asia/Shanghai') as timestamp) from dual;

When converting timestamps and timestamps with time zones, you need to specify The format and time zone information for a date string with time zone. In the above example, the format of the date string with time zone is 'YYYY-MM-DD HH24:MI:SS.FF9', where FF9 represents the 9-digit decimal part, and the time zone information is 'Asia/Shanghai', which represents the East Eighth District. .

3. Conversion between Oracle timestamps and numerical types

Oracle timestamp types can be converted to numerical types, and numerical types can be converted to data types through the cast function. Specifically The conversion method is as follows:

-- Convert timestamp to value
select cast(to_timestamp('2021-10-21 12:00:00', 'YYYY-MM-DD HH24:MI:SS') as number) from dual;
-- Convert numeric value to timestamp
select cast(to_timestamp('1640092800', 'SSSSS') as timestamp) from dual;

Convert timestamp and numeric type When converting, you need to specify the format of the numeric type. In the above example, the value is in the format 'SSSSS', which represents the number of seconds since January 1, 4712 BC.

Summary

This article introduces the conversion method of Oracle timestamp type, including the conversion of timestamp and date type, the conversion of timestamp and timestamp with time zone type, and the conversion of timestamp and numeric type. Convert. When converting timestamps, you need to pay attention to the format of date strings and time zone information, as well as the format of numeric types. Mastering these conversion methods can make it easier to process timestamp type data.

The above is the detailed content of How to convert timestamp in oracle? Method introduction. For more information, please follow other related articles on the PHP Chinese website!