各类date使用

1. 数据库类

oracle:

select sysdate from dual;
select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;
select to_date('2017-10-27 11:34:18','yyyy-mm-dd hh24:mi:ss') from dual;
select trunc(sysdate,'year') from dual;--可替换为month,hh24,mi,
select trunc(sysdate) from dual;
select systimestamp,to_char(systimestamp, 'yyyymmdd hh24:mi:ssxff6') from dual; 
select to_timestamp('2011-09-14 12:52:42.123456789', 'syyyy-mm-dd hh24:mi:ss.ff') from dual;
select cast(to_timestamp('2011-09-14 12:52:42.123456789', 'syyyy-mm-dd hh24:mi:ss.ff') as date) timestamp_to_date from dual;
select cast(sysdate as timestamp) date_to_timestamp from dual;

mysql:

select now();
select DATE_ADD(now(),INTERVAL 1 DAY) 
select date_sub(now(),interval 3 day)
select current_timestamp;
select date_format('2008-08-08 22:23:01', '%Y%m%d%H%i%s');
select str_to_date('08/09/2008', '%m/%d/%Y'); 
select timestamp(date); -- date to timestamp 
set @dt = '2008-09-10 07:15:30.123456';
select date(@dt);        -- 2008-09-10 
select time(@dt);        -- 07:15:30.123456 
select year(@dt);        -- 2008 
select quarter(@dt);     -- 3 
select month(@dt);       -- 9 
select week(@dt);        -- 36 
select day(@dt);         -- 10 
select hour(@dt);        -- 7 
select minute(@dt);      -- 15 
select second(@dt);      -- 30 
select microsecond(@dt); -- 123456

 

postgres:

 

SELECT CURRENT_TIME;--08:01:34.656+05:30
SELECT CURRENT_DATE;-- 2013-05-05
SELECT CURRENT_TIMESTAMP;-- 2013-05-05 08:01:45.375+05:30
select date '2001-09-28' + interval '1 hour';
select date '2001-09-28' + integer '7' --date '2001-10-05'
select date '2001-10-01' - date '2001-09-28'  --3

 

2. 脚本类

 

shell:

date +%s
 date -d "1 day" "+%Y%m%d %A"
 date -d "2 day" "+%Y%m%d %A"
 date -d "-1 month" "+%Y%m%d %A"
 date -d "1 month" "+%Y%m%d %A"
 date -d "-2 day" "+%Y%m%d %A"   或 date --date="2 days ago" "+%Y%m%d %A"
 date -d "-1 day" "+%Y%m%d %A"   或 date --date="1 day ago" "+%Y%m%d %A"
dt1=`date +%s`
dt2=`date +%s -d @${dt1} --date="5 minutes"`
diff=$(($dt2-$dt1));   #300

 

python:

 

import time;  # 引入time模块 
ticks = time.time(); #时间戳
localtime = time.localtime(time.time()); #time.struct_time(tm_year=2016, tm_mon=4, tm_mday=7, tm_hour=10, tm_min=3, tm_sec=27, tm_wday=3, tm_yday=98, tm_isdst=0)
time.strftime("%Y-%m-%d %H:%M:%S", time.localtime());

 

java:

 

import java.text.SimpleDateFormat;
  
java.util.Date date=new Date("2008-04-14");  
SimpleDateFormat sdf=new SimpleDateFormat("yyyy-MM-dd");//小写的mm表示的是分钟  
String dstr="2008-4-24";  
java.util.Date date=sdf.parse(dstr);  
SimpleDateFormat sdf=new SimpleDateFormat("yyyy-MM-dd");  
java.util.Date date=new java.util.Date();  
String str=sdf.format(date);