Time to UNIX timstamp conversion functions for Oracle

Language: English
Programming Language: SQL
Published by: vikaas_bv [not registered]
Last Update: 5/15/2006
Views: 1987

Description

The functions allow the conversion of a date to timestamp and vice-versa in Oracle

Code

Display line numbers

1 -- 2 -- The following functions allow the conversion of oracle date values into 3 -- corresponding timestmaps (unix_timestamp) and vice versa (from_unixtime). 4 -- The function names have been chosen to be the same as that of MYSQL 5 -- for ease of application porting 6 -- 7 create or replace function unix_timestamp(datetime IN date) return number is 8 tmstamp number; 9 time_offset number; 10 epoch date; 11 begin 12 13 -- For the zone GMT+530 (5 * 3600 + 30 * 60) 14 time_offset := 19800; 15 16 -- In Unix EPOCH is 00:00:00 UTC, January 1, 1970 17 epoch := to_date('1970-01-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss'); 18 19 -- Timestamp is the no of seconds from epoch 20 tmstamp := (datetime - epoch) * (24 * 3600) - time_offset; 21 22 return(tmstamp); 23 24 end; 25 / 26 -- 27 create or replace function from_unixtime(timestamp number) return date is 28 tm_date date; 29 time_offset number; 30 epoch date; 31 begin -- For the zone GMT+530 (5 * 3600 + 30 * 60) 32 time_offset := 19800; 33 34 -- In Unix EPOCH is 00:00:00 UTC, January 1, 1970 35 epoch := to_date('1970-01-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss'); 36 37 -- Add the no of days (Fraction in Oracle) to the epoch to arrive at date 38 tm_date := epoch + ((timestamp + time_offset) / (24 * 3600)); 39 40 return(tm_date); 41 42 end; 43 / 44 -- 45

One comment

Thank you - I've been looking for something that can give me seconds since the epoch in Oracle, and this will do the trick.

Monday, January 01, 0001 12:00:00 AM from Alwyn

Add a comment

Name *

Email (won't be displayed) *

Website

Comment *

Sicherheitscode Security Code *