Mysql date_format 与 Oracle to_char(date,’format’)

以上 2 种方法均属于把事件类型的列，转换成你所需要的时间格式的函数。

Oracle: select to_char(zatime, 'yyyymmdd hh24:mi:ss' ) from downloaddata where to_char(zatime, 'yyyymmdd hh24:mi:ss' )> '20120405 23:15:18' and rownum < 5;

TO_CHAR(ZATIME,'YYYYMMDDHH24:M

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

20120406 12:15:30

20120406 12:16:04

20120406 12:16:08

20120406 12:17:17

Mysql: select date_format(CreateDate,'%Y%m%d %H:%i:%s') from tbl_RelatedArticle where date_format(CreateDate,'%Y%m%d %H:%i:%s') >= '20120506 11:58:12' limit 4;

date_format(CreateDate,'%Y%m%d %H:%i:%s')

'20120507 10:51:47'

'20120507 10:52:02'

'20120507 15:20:00'

'20120508 10:13:32'

上面的 2 个例子应该基本上可以了解 date_format 的功能，其实还有一些功能这里暂时不介绍了。下面介绍一下官档中的描述。

（总结：今天也了解了一下 MYSQL 查询中以时间为标准分割查询。）

官档链接

http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_date-format

DATE_FORMAT( date , format )

Formats the date value according to the format string.

The following specifiers may be used in the format string. The “ % ” character is required before format specifier characters.

Specifier	Description
%a	Abbreviated weekday name ( Sun .. Sat )
%b	Abbreviated month name ( Jan .. Dec )
%c	Month, numeric ( 0 .. 12 )
%D	Day of the month with English suffix ( 0th , 1st , 2nd , 3rd , …)
%d	Day of the month, numeric ( 00 .. 31 )
%e	Day of the month, numeric ( 0 .. 31 )
%f	Microseconds ( 000000 .. 999999 )
%H	Hour ( 00 .. 23 )
%h	Hour ( 01 .. 12 )
%I	Hour ( 01 .. 12 )
%i	Minutes, numeric ( 00 .. 59 )
%j	Day of year ( 001 .. 366 )
%k	Hour ( 0 .. 23 )
%l	Hour ( 1 .. 12 )
%M	Month name ( January .. December )
%m	Month, numeric ( 00 .. 12 )
%p	AM or PM
%r	Time, 12-hour ( hh:mm:ss followed by AM or PM )
%S	Seconds ( 00 .. 59 )
%s	Seconds ( 00 .. 59 )
%T	Time, 24-hour ( hh:mm:ss )
%U	Week ( 00 .. 53 ), where Sunday is the first day of the week
%u	Week ( 00 .. 53 ), where Monday is the first day of the week
%V	Week ( 01 .. 53 ), where Sunday is the first day of the week; used with %X
%v	Week ( 01 .. 53 ), where Monday is the first day of the week; used with %x
%W	Weekday name ( Sunday .. Saturday )
%w	Day of the week ( 0 =Sunday.. 6 =Saturday)
%X	Year for the week where Sunday is the first day of the week, numeric, four digits; used with %V
%x	Year for the week, where Monday is the first day of the week, numeric, four digits; used with %v
%Y	Year, numeric, four digits
%y	Year, numeric (two digits)
%%	A literal “ % ” character
% x	x , for any “ x ” not listed above

Ranges for the month and day specifiers begin with zero due to the fact that MySQL permits the storing of incomplete dates such as '2014-00-00' .

The language used for day and month names and abbreviations is controlled by the value of the lc_time_names system variable ( Section 10.7, “MySQL Server Locale Support” ).

DATE_FORMAT() returns a string with a character set and collation given by character_set_connection and collation_connection so that it can return month and weekday names containing non-ASCII characters.

mysql> SELECT DATE_FORMAT('2009-10-04 22:23:00', '%W %M %Y');

-> 'Sunday October 2009'

mysql> SELECT DATE_FORMAT('2007-10-04 22:23:00', '%H:%i:%s');

-> '22:23:00'

mysql> SELECT DATE_FORMAT('1900-10-04 22:23:00',

-> '%D %y %a %d %m %b %j');

-> '4th 00 Thu 04 10 Oct 277'

mysql> SELECT DATE_FORMAT('1997-10-04 22:23:00',

-> '%H %k %I %r %T %S %w');

-> '22 22 10 10:23:00 PM 22:23:00 00 6'

mysql> SELECT DATE_FORMAT('1999-01-01', '%X %V');

-> '1998 52'

mysql> SELECT DATE_FORMAT('2006-06-00', '%d');

-> '00'

oracle ：ROUND ，TRUNC，TO_CHAR 分割时间查询。

http://space.itpub.net/26442936/viewspace-722415

ITPub博客

Mysql date_format 与 Oracle to_char(date,’format’)

最新文章