oracle number转为timestamp timestamp转number

大家好，又见面了，我是你们的朋友全栈君。

现在平台有个字段是用来记录插入时间的，但是是用number型存储，想转为时间类型的。
结果：
SELECT TO_CHAR(字段名 / (1000 * 60 * 60 * 24) + TO_DATE(‘1970-01-01 08:00:00’, ‘YYYY-MM-DD HH24:MI:SS’), ‘YYYY-MM-DD HH24:MI:SS’) AS CDATE FROM 表名;
解决问题的过程：
http://blog.csdn.net/a9529lty/article/details/5306622

ORACLE 毫秒转换为日期日期转换毫秒
日期转换毫秒
SELECT TO_NUMBER(sysdate – TO_DATE(‘1970-01-01 8:0:0’, ‘YYYY-MM-DD HH24:MI:SS’)) * 24 * 60 * 60 * 1000 FROM DUAL;
SELECT TO_NUMBER(TO_DATE(‘2005-03-29 12:30:45’, ‘YYYY-MM-DD HH24:MI:SS’) – TO_DATE(‘1970-01-01 8:0:0’, ‘YYYY-MM-DD HH24:MI:SS’)) * 24 * 60 * 60 * 1000 FROM DUAL;

毫秒转换日期
SELECT TO_CHAR(1112070645000 / (1000 * 60 * 60 * 24) + TO_DATE(‘1970-01-01 08:00:00’, ‘YYYY-MM-DD HH24:MI:SS’), ‘YYYY-MM-DD HH24:MI:SS’) AS CDATE FROM DUAL;
SELECT TO_CHAR(1336986532000 / (1000 * 60 * 60 * 24) + TO_DATE(‘1970-01-01 08:00:00’, ‘YYYY-MM-DD HH24:MI:SS’), ‘YYYY-MM-DD HH24:MI:SS’) AS CDATE FROM dual ;

在Oracle中，默认没有直接提供获取当前时间的毫秒数的相关function，所以要想在SQL里面获得毫秒数，只好手动计算

SQL> select to_char(sysdate,’yyyy-mm-dd hh24:mi:ss’) current_date, (sysdate- to_date(‘1970-01-01′,’yyyy-mm-dd’)) * 86400000 current_milli from dual;

CURRENT_DATE CURRENT_MILLI
——————- ————-
2009-01-06 14:00:09 1231250409000
–1000*60*60*24一天毫秒数 = 86400000
方法:
日期a- 日期b=相差天数.
一天毫秒数 = 1000*60*60*24(86400000)
select to_char(sysdate, ‘yyyy-mm-dd hh24:mi:ss’) current_date,(sysdate – to_date(‘1970-01-01′,’yyyy-MM-dd’)) * 86400000 current_milli from dual;
select to_date(‘1970-01-01′,’yyyy-MM-dd’)+1261674301000/86400000 from dual;
select to_date(‘1970-01-01′,’yyyy-MM-dd’)+当前日期的毫秒数/86400000 from dual;
http://space.itpub.net/8554499/viewspace-659931

ORACLE中用SQL实现时间日期由毫秒数到字符串的转换
在开发过程中，为了方便，经常将时间日期的毫秒数以整形的格式存到数据库中，虽然方便了不同地方的处理，但也会增加些麻烦。比如，我要到数据库里直接看某个时间的值，都是一长串的数字，鬼都看不懂，想到看到该字段的值，还要写个转换程序，把毫秒数在转换成日期时间类型，或者拿个计算器来算。要查看的记录少，还能将就，如果多了，那不崩溃才怪呢。于是想到了在SQL里直接转换的方法，还好牛人多，在网上搜了一下，还真有相关的东西，原始的语句如下：
SELECT TO_CHAR(SYSDATE,’YYYY-MM-DD HH24:MI:SS’) CURRENT_DATE, (SYSDATE- TO_DATE(‘1970-01-01′,’YYYY-MM-DD’)) * 86400000 CURRENT_MILLI FROM DUAL;

这只是个例子，还不能直接解决上面的问题，所有就改了一下，是以自定义函数的形式存在的，代码如下：
CREATE OR REPLACE FUNCTION DATELONG2TEXT (PARAM IN LONG) RETURN VARCHAR2 AS
V_TEXT VARCHAR2(64);
BEGIN
SELECT TO_CHAR(TO_DATE(‘1970-01-01’, ‘YYYY-MM-DD’)+(PARAM/(24*60*60*1000) + 8/24 – 1/86400),’YYYY-MM-DD HH24:MI:SS’) INTO V_TEXT FROM DUAL;
RETURN V_TEXT;
END DATELONG2TEXT;
使用示例：
SELECT TO_CHAR(SYSDATE, ‘YYYY-MM-DD HH24:MI:SS’),SYSDATE,DATELONG2TEXT((SYSDATE – 8 / 24 – TO_DATE(‘1970-01-01’, ‘YYYY-MM-DD’)) * 86400000) FROM DUAL;
说明：
1、加上 8/24 ，是因为时间格式是GMT，结果会受时区的影响，我们在东8区，所以要加上8个小时；
2、减1/86400，至于为什么，我也说不清楚，只是在实际当中确实多了1秒，只好以这种方式减去了，根据时间情况确定是否进行调整。
当然，我们也可以类似的写出将日期由字符串转换成毫秒数的函数。

http://hi.baidu.com/78347079/blog/item/14a05afd868d291f08244d15.html

ORACLE自动插入当前时间的年月日YYYY-MM-DD格式的实现

oracle没有date()函数,sysdate函数的值是包括时分秒的,要实现插入当前时间默认值还真麻烦.

只好自己写储存过程,而字段默认值里面又不能调用储存过程,还得写个触发器!而储存过程里面取出来的sysdate前几位的只却变成17-11月-07的格式了,不是自己想要的,2007-11-17的格式,又得单独取年月日再组合起来,这样一来返回值就不能是日期类型而要字符类型了.

一个字,烦!不过还是把他实现了.下面把代码给大家分享一下,如果有更好的方法麻烦告知.

1.储存过程

CREATE OR REPLACE FUNCTION “GET_DATE” RETURN VARCHAR2

IS

yyyy varchar2(36);

mm varchar2(36);

dd varchar2(36);

tempdate varchar2(36);

BEGIN

tempdate := ”;

select to_char(to_date(sysdate),’YYYY’) into yyyy from dual;

select to_char(to_date(sysdate),’MM’) into mm from dual;

select to_char(to_date(sysdate),’DD’) into dd from dual;

tempdate := substr(yyyy,1,4)|| ‘-‘||

substr(mm,1,2)|| ‘-‘||

substr(dd,1,2)

;

return tempdate;

END;

2.触发器

CREATE OR REPLACE TRIGGER STATWEEK_tg

–STATWEEK fdate 的触发器

BEFORE INSERT ON STATWEEK FOR EACH ROW

BEGIN

SELECT get_date INTO :NEW.fdate FROM DUAL;

END;

注:这里的STATWEEK为对应的数据表 fdate为自动增长的字段,get_date为对应的储存过程名.

3.数据表

— Create table

create table STATWEEK

(

星期一 VARCHAR2(20) default 0,

星期二 VARCHAR2(20) default 0,

星期三 VARCHAR2(20) default 0,

星期四 VARCHAR2(20) default 0,

星期五 VARCHAR2(20) default 0,

星期六 VARCHAR2(20) default 0,

星期日 VARCHAR2(20) default 0,

TWEEK VARCHAR2(10),

ADMIN VARCHAR2(50),

FDATE VARCHAR2(20)

)