mysql date to long_数据库中日期字段与long转换

oracle- java

1.date转long :

当前时间转成long :

秒级(mysql可直接用)

SELECT (SYSDATE - TO_DATE('1970-1-1 8', 'YYYY-MM-DD HH24')) * 86400 FROMDUAL;

毫秒级

SELECT (SYSDATE - TO_DATE('1970-1-1 8', 'YYYY-MM-DD HH24')) * 86400000 + TO_NUMBER(TO_CHAR(SYSTIMESTAMP(3), 'FF')) AS MILLIONS FROMDUAL;

2.long转date

select to_date('1970-01-01 8','yyyy-mm-dd hh24')+ 1439278552779/1000/24/60/60 from dual

mysql

mysql 中和oracle 的时间数值差3位,精确到秒，因此如果在JAVA中操作，记得要 /1000

从5+ 版后增加了dual表，用惯oracle的可能比较喜欢

1.当前时间

select now() from

dual

2.当前时间转成数值

SELECT UNIX_TIMESTAMP(DATE(NOW())) FROM

dual

3、相互转换

SELECT UNIX_TIMESTAMP(STR_TO_DATE("2015-8-29 11:12:51",'%Y-%m-%d

%H:%i:%s')) FROM DUAL

结果：1440817971

SELECT FROM_UNIXTIME(1440817971)  FROM DUAL

结果：2015-08-29 11:12:51

4.数值转成日期，如果是毫秒级的数值要/1000(oracle为毫秒级)

SELECT FROM_UNIXTIME(1241450728000/1000) FROM

dual

转换时如果出现date为null，oracle中需要处理

select id,credit,period,mycredit,myperiod,

decode(firstlearningdate,null,0,(firstlearningdate-

TO_DATE('1970-1-1 8', 'YYYY-MM-DD HH24')) * 86400) as

firsttime,decode(lastlearningdate,null,0,(lastlearningdate-

TO_DATE('1970-1-1 8', 'YYYY-MM-DD HH24')) * 86400) as

lasttime,chargeyear,status,learningtimes,cid,usid from

oe_my_course

decode(firstlearningdate,null,0,(firstlearningdate-

TO_DATE('1970-1-1 8', 'YYYY-MM-DD HH24')) * 86400) as

firsttime

即 如果为null则设0， 否则....