ITPub博客

Our billing system date format is using unix epoch time. converison like the following:oracle time --> unix epoch timeselect (sysdate - to_date('1970-01-01 00:00:00','YYYY-MM-DD HH24:MI:SS')) * 86400 - substr(TZ_OFFSET(sessiontimezone),1,instr(TZ_OFFSET(sessiontimezone),':')-1) * 3600 from dual;unix epoch time-->oracle timeselect from_tz(cast(to_date('1970-01-01 00:00:00','YYYY-MM-DD HH24:MI:SS') as timestamp),'Australia/NSW') at local + numtodsinterval(1305882706,'SECOND') from dual;you c

our date dimension surrogate key is built by Julian day. (Julian day: the number of days since January 1, 4712 BC. ) The following are some operations on that:1.Julian date-->normal oracle date:SQL> select date_sk,to_date(date_sk,'j') normal_date from dim_date2 where date_sk<>0 and rownum<10;DATE_SK NORMAL_DA---------- ---------2451545 01/JAN/002451546 02/JAN/002451547 03/JAN/002451548 04/JAN/002451549 05/JAN/002451550 06/JAN/002451551 07/JAN/002451552 08/JAN/002451553 09/JAN/002.

If the escape character precedes the character % or _ in the pattern, then Oracle interprets this character literally in the pattern rather than as a special pattern-matching charactere.g.SELECT * FROM dba_tablesWHERE table_name LIKE '%GL_%' ESCAPE ''[@more@]

q'! text-to-be-quoted !'! is a signle character, called the quote delimiter. with the quote operator, apostrophes don't have to be dboule:e.g. select q'#Oracle's quote operator#' quote_test from dual;QUOTE_TEST--------------------------Oracle's quote operatorit's very helpful and convenience to use the quote, particularly when you want to update or insert a very long and big sql. e.g:DECLARE l_sql VARCHAR2(32767);BEGIN l_sql := q'! select ********** very very long sql here !'; UPDATE t

本博开编，写些什么呢？先介绍一下博主吧：本人Felix, 2010.7月混完硕士毕业，9月顺利入职澳洲电信Telstra.头衔是数据仓库专家(specialit,不是expert 中文翻译不准，但不知道还能怎么翻*.*)报表开发员(data warehouse specialist report developer) . 国内四年半的电信从业经验，但做的较杂.然后又过来念了两年多的书，其实心里挺虚的，一大把年龄了，好像什么都会但没一个精的。现在就准备把全身心投入到 数据仓库方向，准备未来十年或者几十年内就用他混饭吃了(在国外可以不用担心程序员30岁问题,可以活到老程序到老. 我虽然年