How to convert Oracle timestamp to different datetime formats

Oracle database uses timestamp (timestamp) as the date and time type. When dealing with date and time issues, it is often necessary to convert the timestamp into a human-readable date and time format. In this article, we will discuss how Oracle timestamps are converted to different date time formats.

1. Convert timestamp to date-time string

We can use Oracle built-in function to_char() to convert timestamp to date-time string. The first parameter of the to_char() function is the timestamp that needs to be converted, and the second parameter is the string representation of the target date and time format.

For example, to convert a timestamp to a datetime string in the 'YYYY-MM-DD HH24:MI:SS' format, you can use the following code:

SELECT to_char(TIMESTAMP '2021-08-31 12:00:00', 'YYYY-MM-DD HH24:MI:SS') AS datetime_string
FROM dual;

The result should look like this of:

DATETIME_STRING
-------------------
2021-08-31 12:00:00

2. Convert timestamp to Unix time

Unix time refers to the time from January 1, 1970 00:00:00 UTC to the specified time the number of seconds between. In Oracle, we can convert timestamp to Unix time using the following code:

SELECT (TO_TIMESTAMP('1970-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS') - TIMESTAMP '2021-08-31 12:00:00') * 24 * 60 * 60 AS unix_time
FROM dual;

Explain this code. First, we use the TO_TIMESTAMP() function to convert the string '1970-01-01 00:00:00' to a timestamp type. Then, we subtract the specified timestamp from this timestamp (here is '2021-08-31 12:00:00') to get a time difference (duration). Finally, multiply this time difference by 24 * 60 * 60, which is the number of seconds in a day, to get the corresponding Unix time.

The output result of the above code should be an integer representing the number of seconds between January 1, 1970 00:00:00 UTC and the specified time.

3. Convert Unix time to timestamp

If we want to convert Unix time to timestamp, we can use the following code:

SELECT TO_TIMESTAMP('1970-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS') + NUMTODSINTERVAL(1630425600, 'SECOND') AS timestamp
FROM dual;

In the above code, we use the TO_TIMESTAMP() function to convert the string '1970-01-01 00:00:00' into a timestamp type. We then use the NUMTODSINTERVAL() function to convert the Unix time (here 1630425600) to an interval type. Finally, add the timestamp and time interval to get the timestamp corresponding to Unix time.

The output result should be a timestamp type value, indicating the date and time corresponding to Unix time.

Summary

This article introduces the Oracle timestamp conversion method, which can convert timestamps into different date and time formats. Through these methods, we can deal with date and time issues more conveniently.

The above is the detailed content of How to convert Oracle timestamp to different datetime formats. For more information, please follow other related articles on the PHP Chinese website!