首页 文章

Oracle将日期转换为数字

提问于
浏览
0

我想在oracle 11g中将日期转换为数字 .

日期存储在(2013年3月12日12:23:00) .

我想将其转换为Number(日期对应的数字值) . 在java中,我们将日期转换为long .

我希望在这里完成同样的事情 .

Calendar c = Calendar.getInstance();
    c.set(2013, 05, 23, 0, 0, 0);

    Date date = c.getTime();
    System.out.println("Date is  " + date);
    long longDate = date.getTime();
    System.out.println("Date as long :" + longDate);
    Date d = new Date(longDate);
    System.out.println("Converted Date :" + d);*

输出是:

**日期为2013年3月23日00:00:00 SGT

日期长:1371916800981

转换日期:Sun Jun 23 00:00:00 SGT 2013 **

现在我想将值存储为1371916800981

2 回答

  • 0

    我猜你想要的长数据类型就像1970-01-01以来的秒数或毫秒数 .

    要得到这个只需要一点算术:

    select (to_date(s1, 'MM/DD/YYYY HH24:MI:SS') -
            to_date('1970-01-01', 'YYYY-MM-DD')
           ) *24*60*60
    from (select '12/03/2013 12:23:00' as s1 from dual
         ) t
    

    我注意到您的结果是使用当前时间戳 . 这可能包括此常量日期格式不包括的毫秒数 .

  • 0

    您可以借助RAW数据类型实现此目的:

    SELECT 
      s1,
      to_number(TO_CHAR(s1,'YYYYMMDDHH24MISS')) as s2,
      utl_raw.cast_to_raw(s1) as d1,
      utl_raw.cast_to_raw(s2) as d2,
      round(utl_raw.cast_to_number(utl_raw.cast_to_raw(s1)),20) as s1,
      round(utl_raw.cast_to_number(utl_raw.cast_to_raw(s2)),20) as s2,
      utl_raw.cast_to_number(utl_raw.cast_to_raw(s1)) - utl_raw.cast_to_number(utl_raw.cast_to_raw(s2)) as s1_s2_diff
    FROM 
    ( select 
        sysdate as s1, 
        sysdate-1/24/60/60 as s2 
      from dual );
    

    输出:

    S1                  S2      
    ------------------- --------------
    2014.03.16 11:14:16 20140316111416               
    
    D1                                     D2
    -------------------------------------- --------------------------------------
    323031342E30332E31362031313A31343A3136 323031342E30332E31362031313A31343A3135
    
    S1_1                                   S2_1    
    -------------------------------------- --------------------------------------
    -53524955535055524769525243,5249435247 -53524955535055524769525243,5249435248
    
    SDIFF     
    ------------    
    0,0000000001
    

相关问题