[20181201]stamp 转化 time.txt

--//昨天看https://jonathanlewis.wordpress.com/2018/11/27/dump-logfile/,里面提到time到stamp的转化.

--//that expresses the time as the number of seconds since Jan 1988, with the unfortunate simplification that Oracle

--//thinks there are 31 days in every month of the year:

--//想起以前自己的一些错误.不过看看人家写time到stamp的转化,简单许多,自己按照国人的日期习惯修改如下:

WITH t1 AS (SELECT TO_DATE ('&&1', 'yyyy-mm-dd hh24:mi:ss') tt FROM DUAL)

SELECT tt time

      ,  round(86400

       * (    31

            * MONTHS_BETWEEN

              (

                 TRUNC (tt, 'MM')

                ,TO_DATE ('1988-01-01', 'yyyy-mm-dd')

              )

          + tt

          - TRUNC (tt, 'MM')),0)

          stamp

  FROM t1;

--//相比我以前从网上抄的,就比较麻烦:

$ cat convstamp.sql

SELECT  '&&1' time,(((((yyyy - 1988) * 12 + mm - 1) * 31 + dd - 1) * 24 + hh) * 60 + mi)* 60 + ss  stamp

  FROM (SELECT TO_CHAR (TO_DATE ('&&1', 'yyyy-mm-dd hh24:mi:ss'), 'yyyy')

                  yyyy

              ,TO_CHAR (TO_DATE ('&&1', 'yyyy-mm-dd hh24:mi:ss'), 'mm') mm

              ,TO_CHAR (TO_DATE ('&&1', 'yyyy-mm-dd hh24:mi:ss'), 'dd') dd

              ,TO_CHAR (TO_DATE ('&&1', 'yyyy-mm-dd hh24:mi:ss'), 'hh24') hh

              ,TO_CHAR (TO_DATE ('&&1', 'yyyy-mm-dd hh24:mi:ss'), 'mi') mi

              ,TO_CHAR (TO_DATE ('&&1', 'yyyy-mm-dd hh24:mi:ss'), 'ss') ss

          FROM DUAL);

--//另外可以提一下,stamp不是连续的,特别是闰年的2月跳跃更大.

SCOTT@test01p> @ convstamp '2018-02-28 23:59:59'

TIME                     STAMP

------------------- ----------

2018-02-28 23:59:59  969321599

SCOTT@test01p> @ convstamp '2018-03-01 00:00:00'

TIME                     STAMP

------------------- ----------

2018-03-01 00:00:00  969580800

--//969580800-969321599 = 259201,存在跳跃.

--//顺便贴上stamp转化到时间的语句.

$ cat stamp.sql

SELECT &&1 stamp,to_date(yyyy||'/'||mm||'/'||dd||' '||hh||':'||mi||':'||ss,'yyyy-mm-dd hh24:mi:ss') stamp_conv_time  from (

SELECT &&1

        ,FLOOR (&&1 / (86400*31*12))+1988 yyyy

        ,FLOOR (MOD (&&1 / (86400*31),12))+1 mm

        ,FLOOR (MOD (&&1 / 86400, 31))+1 dd

        ,FLOOR (MOD (&&1 / 3600, 24)) hh

        ,FLOOR (MOD (&&1 / 60, 60)) mi

        ,MOD (&&1, 60) ss

        from dual);

--//取969321599,969580800之间一个数值.

SCOTT@test01p> set verify off

SCOTT@test01p> @ stamp.sql 969580780

SELECT 969580780 stamp,to_date(yyyy||'/'||mm||'/'||dd||' '||hh||':'||mi||':'||ss,'yyyy-mm-dd hh24:mi:ss') stamp_conv_time  from (

                                                                             *

ERROR at line 1:

ORA-01839: date not valid for month specified

--//报如上错误.