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

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博客 ” ，链接：http://blog.itpub.net/26442936/viewspace-723051/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/26442936/viewspace-723051/