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

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

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

Oracle:  selectto_char(zatime,'yyyymmdd hh24:mi:ss')from downloaddata whereto_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 thedatevalue according to theformatstring.

The following specifiers may be used in theformatstring. 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	AMorPM
%r	Time, 12-hour (hh:mm:ssfollowed byAMorPM)
%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 thelc_time_namessystem variable (Section 10.7, “MySQLServer Locale Support”).

DATE_FORMAT()returns a string with a character set and collation given bycharacter_set_connectionandcollation_connectionso 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://blog.csdn.net/lovehewenyu/article/details/7546787