Let’s talk about the conversion method of Oracle timestamp

The timestamp used in the Oracle database is based on Greenwich Time and is an 18-digit number stored in binary. For the average user, this timestamp is not intuitive and requires conversion. This article will introduce the Oracle timestamp conversion method.

1. The structure of Oracle timestamp

Oracle’s timestamp consists of two parts:

1. Date part (7 bytes): contains date information , expressed as the number of days since January 1, 1601.

2. Time part (1 byte): Contains time information, expressed in units of 100 nanoseconds.

2. Convert timestamp to date format

If you need to convert Oracle timestamp to date format, you can use the following SQL statement:

SELECT TO_DATE('1970 -01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS') (timestamp_column / (246060)) AS converted_date
FROM your_table;

Among them, timestamp_column is the column name where the timestamp is located, and your_table is the table name. The function of the above SQL statement is to convert the timestamp into the number of seconds since January 1, 1970, and add the timestamp of that date to get the actual date value.

3. Convert the date format into a timestamp

If you need to convert the date format into an Oracle timestamp, you can use the following SQL statement:

SELECT (CAST(NEW_TIME (date_column, 'GMT', 'UTC') AS TIMESTAMP) - TO_TIMESTAMP('1970-01-01 00:00:00 UTC', 'YYYY-MM-DD HH24:MI:SS TZR')) ( 2460601000000000) AS converted_timestamp
FROM your_table;

Among them, date_column is the column name where the date is located, and your_table is the table name. The function of the above SQL statement is to subtract the timestamp of January 1, 1970 from the timestamp of the specified date, thereby obtaining the timestamp starting from January 1, 1970 (unit is 100 nanoseconds).

It should be noted that Oracle timestamps are accurate to 100 nanoseconds, while Unix timestamps (the number of seconds starting from 00:00:00 on January 1, 1970) are accurate to seconds. Therefore, pay extra attention when converting it.

4. Summary

The above method can be used to convert Oracle timestamp and ordinary time format, but it is necessary to note that their accuracy is different. If you want to accurately transfer timestamp information between different systems, you need to be careful.

The above is the detailed content of Let’s talk about the conversion method of Oracle timestamp. For more information, please follow other related articles on the PHP Chinese website!