oracle 时间函数

 

select sysdate,

sysdate {-|+} [5*] interval '8' {SECOND|MINUTE|HOUR|DAY|MONTH\YEAR} from dual;

 

select sysdate,to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;

 

select to_date('2006-09-21 21:15:37','yyyy-mm-dd hh24:mi:ss') from dual;

 

当前时间 年月日小时分秒毫秒:

　　select to_char(current_timestamp(5),'DD-MON-YYYY HH24:MI:SSxFF') from dual;

当前 时间的秒毫秒，可以指定秒后面的精度(最大=9)

　　select to_char(current_timestamp(9),'MI:SSxFF') from dual;

 

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

Datetime format elements are used in , and .

AD	AD indicator
A.D.	A.D. indicator
AM	Meridian indicator
A.M.	Meridian indicator
BC	BC indicator
B.C.	B.C. indicator
CC	Century
SCC	Century
D	Day of week (between 1 and 7)
DAY	Spelled name of day (char(9))
DD	Day of month (between 1 and 31)
DDD	Day of year (between 1 and 366)
DL	long date format
DS	short date format
DY	abbreviated name of day (char(3))
EE	name of era
E	abbreviated name of era
FFx	x=1 through 9: fraction of seconds
FM	no leading or trailing blanks
FX	requires exact matching between character string and format
HH	hour of the day (between 1 and 12)
HH12	hour of the day (between 1 and 12)
HH24	hour of the day (between 1 and 24)
IW	week of the year (between 1 and 53)
IYYY	4 digit ISO year
IYY	last three digits of ISO year
IY	last two digits of ISO year
I	last digit of ISO year
J	julian day (numbers of days since 1st of January 4712 B.C)
MI	minute (between 0 and 59)
MM	month (between 1 and 12)
MONTH	name of month (char(9))
MON	abbreviated name of month (char(3))
PM	PM indicator
P.M.	P.M. indicator
Q	quarter of year
RM	roman month (I .. XII)
RR	two digit years
RRRR	???
SSSSS	seconds since midnight
TS	???
TZD	Daylight saving information
TZH	timezone hour
WW	week of year (first week days 1 through 7 in year)
W	week of month (first week days 1 through 7 in month)
X	local radix character
Y,YYY	year with comma (wondering who uses that...)
YEAR	year spelled
SYEAR	year spelled, BC dates prefixed with -
YYYY	four digit year
SYYYY	four digit year, BC dates prefixed with -
YYY	last three digits of year
YY	last two digits of year
Y	last digit of year