Time to UNIX timstamp conversion functions for Oracle

Sprache: English
Programmiersprache: SQL
Veröffentlicht von: vikaas_bv [nicht registriert]
Letzte Änderung: 15.05.2006
Aufrufe: 3597

Lizenz: GNU General Public License

Beschreibung

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

Code

Zeilennummern anzeigen

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

Ein Kommentar

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

Montag, 1. Januar 0001 00:00:00 von Alwyn

Dieses Snippet kommentieren

Name *

E-Mail (wird nicht angezeigt) *

Website

Kommentar *

Sicherheitscode *