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