[PL/SQL] How to convert date to Unix Timestamp

[PL/SQL] How to convert date to Unix Timestamp

Unix Timestamp describes the point in time as the number of seconds that have elapsed since 00:00:00 (UTC), Thursday, 1 January 1970. The simple function implemented in this post helps you to calculate Unix Timestamp in Oracle Database. It can be useful in many different cases; i.e. integration with Java.

Below is an example of Unix Timestamp. More about it you cam find here.

Date Unix Timestamp
2018-05-28 18:00:55 1527530455

How to calculate Unix Timestamp in PL/SQL

Oracle Database hasn’t implemented UUID function yet, but you can use the following function:

function unix_timestamp(pi_date date) return number is
    c_base_date constant date := to_date('1970-01-01', 'YYYY-MM-DD');
    c_seconds_in_day constant number := 24 * 60 * 60;
    v_unix_timestamp number;
    v_unix_timestamp := trunc((pi_date - c_base_date) * c_seconds_in_day);
    if (v_unix_timestamp < 0 ) then
        raise_application_error(-20000, 'unix_timestamp:: unix_timestamp cannot be nagative');
    end if;
    return v_unix_timestamp;
end unix_timestamp;
At the end… May I ask you for something?

If I helped you solve your problem, please share this post. Thanks to this, I will have the opportunity to reach a wider group of readers. Thank You

5 1 vote
Article Rating
Notify of
Inline Feedbacks
View all comments