Функции даты и времени MySQL06.12.2007 00:00

Необходимо обрабатывать строки в  зависимости от даты, разделить список как бы на 2 части- анонсы и отчеты. Выбор пал на функцию  UNIX_TIMESTAMP.

SELECT *, UNIX_TIMESTAMP(date_start) as uts_start FROM table ORDER BY date_start 

В результате я могу сравнивать с текущим временем:

if($data['uts_start']<time()) { ... }

Все функции даты и времени: 

Date and Time Functions
-----------------------

See *Note Date and time types:: for a description of the range of values
each type has and the valid formats in which date and time values may be
specified.

Here is an example that uses date functions.  The query below selects
all records with a `date_col' value from within the last 30 days:

     mysql> SELECT something FROM table
                WHERE TO_DAYS(NOW()) - TO_DAYS(date_col) <= 30;

`DAYOFWEEK(date)'
     Returns the weekday index

     for `date' (`1' = Sunday, `2' = Monday, ... `7' = Saturday).
     These index values correspond to the ODBC standard:

          mysql> select DAYOFWEEK('1998-02-03');
                  -> 3

`WEEKDAY(date)'
     Returns the weekday index for `date' (`0' = Monday, `1' = Tuesday,
     ... `6' = Sunday):

          mysql> select WEEKDAY('1997-10-04 22:23:00');
                  -> 5
          mysql> select WEEKDAY('1997-11-05');
                  -> 2

`DAYOFMONTH(date)'
     Returns the day of the month for `date', in the range `1' to `31':

          mysql> select DAYOFMONTH('1998-02-03');
                  -> 3

`DAYOFYEAR(date)'
     Returns the day of the year for `date', in the range `1' to `366':

          mysql> select DAYOFYEAR('1998-02-03');
                  -> 34

`MONTH(date)'
     Returns the month for `date', in the range `1' to `12':

          mysql> select MONTH('1998-02-03');
                  -> 2

`DAYNAME(date)'
     Returns the name of the weekday for `date':

          mysql> select DAYNAME("1998-02-05");
                  -> 'Thursday'

`MONTHNAME(date)'
     Returns the name of the month for `date':

          mysql> select MONTHNAME("1998-02-05");
                  -> 'February'

`QUARTER(date)'
     Returns the quarter of the year for `date', in the range `1' to
     `4':

          mysql> select QUARTER('98-04-01');
                  -> 2

`WEEK(date)'
`WEEK(date,first)'
     With a single argument, returns the week for `date', in the range
     `0' to `53' (yes, there may be the beginnings of a week 53), for
     locations where Sunday is the first day of the week.  The
     two-argument form of `WEEK()' allows you to specify whether the
     week starts on Sunday or Monday.  The week starts on Sunday if the
     second argument is `0', on Monday if the second argument is `1':

          mysql> select WEEK('1998-02-20');
                  -> 7
          mysql> select WEEK('1998-02-20',0);
                  -> 7
          mysql> select WEEK('1998-02-20',1);
                  -> 8
          mysql> select WEEK('1998-12-31',1);
                  -> 53

`YEAR(date)'
     Returns the year for `date', in the range `1000' to `9999':

          mysql> select YEAR('98-02-03');
                  -> 1998

`YEARWEEK(date)'
`YEARWEEK(date,first)'
     Returns year and week for a date.  The second arguments works
     exactly like the second argument to `WEEK()'.  Note that the year
     may be different from the year in the date argument for the first
     and the last week of the year:

          mysql> select YEARWEEK('1987-01-01');
                  -> 198653

`HOUR(time)'
     Returns the hour for `time', in the range `0' to `23':

          mysql> select HOUR('10:05:03');
                  -> 10

`MINUTE(time)'
     Returns the minute for `time', in the range `0' to `59':

          mysql> select MINUTE('98-02-03 10:05:03');
                  -> 5

`SECOND(time)'
     Returns the second for `time', in the range `0' to `59':

          mysql> select SECOND('10:05:03');
                  -> 3

`PERIOD_ADD(P,N)'
     Adds `N' months to period `P' (in the format `YYMM' or `YYYYMM').
     Returns a value in the format `YYYYMM'.

     Note that the period argument `P' is _not_ a date value:

          mysql> select PERIOD_ADD(9801,2);
                  -> 199803

`PERIOD_DIFF(P1,P2)'
     Returns the number of months between periods `P1' and `P2'.  `P1'
     and `P2' should be in the format `YYMM' or `YYYYMM'.

     Note that the period arguments `P1' and `P2' are _not_ date values:

          mysql> select PERIOD_DIFF(9802,199703);
                  -> 11

`DATE_ADD(date,INTERVAL expr type)'
`DATE_SUB(date,INTERVAL expr type)'
`ADDDATE(date,INTERVAL expr type)'
`SUBDATE(date,INTERVAL expr type)'
     These functions perform date arithmetic.  They are new for *MySQL*
     Version 3.22.  `ADDDATE()' and `SUBDATE()' are synonyms for
     `DATE_ADD()' and `DATE_SUB()'.

     In *MySQL* Version 3.23, you can use `+' and `-' instead of
     `DATE_ADD()' and `DATE_SUB()' if the expression on the right side
     is a date or datetime column. (See example)

     `date' is a `DATETIME' or `DATE' value specifying the starting
     date.  `expr' is an expression specifying the interval value to be
     added or substracted from the starting date.  `expr' is a string;
     it may start with a `-' for negative intervals.  `type' is a
     keyword indicating how the expression should be interpreted.

     The `EXTRACT(type FROM date)' function returns the 'type' interval
     from the date.

     The following table shows how the `type' and `expr' arguments are
     related:

     `type' *value*                     *Expected* `expr' *format*
     `SECOND'                           `SECONDS'
     `MINUTE'                           `MINUTES'
     `HOUR'                             `HOURS'
     `DAY'                              `DAYS'
     `MONTH'                            `MONTHS'
     `YEAR'                             `YEARS'
     `MINUTE_SECOND'                    `"MINUTES:SECONDS"'
     `HOUR_MINUTE'                      `"HOURS:MINUTES"'
     `DAY_HOUR'                         `"DAYS HOURS"'
     `YEAR_MONTH'                       `"YEARS-MONTHS"'
     `HOUR_SECOND'                      `"HOURS:MINUTES:SECONDS"'
     `DAY_MINUTE'                       `"DAYS HOURS:MINUTES"'
     `DAY_SECOND'                       `"DAYS HOURS:MINUTES:SECONDS"'

     *MySQL* allows any punctuation delimiter in the `expr' format.
     Those shown in the table are the suggested delimiters.  If the
     `date' argument is a `DATE' value and your calculations involve
     only `YEAR', `MONTH', and `DAY' parts (that is, no time parts), the
     result is a `DATE' value.  Otherwise the result is a `DATETIME'
     value:

          mysql> SELECT "1997-12-31 23:59:59" + INTERVAL 1 SECOND;
                  -> 1998-01-01 00:00:00
          mysql> SELECT INTERVAL 1 DAY + "1997-12-31";
                  -> 1998-01-01
          mysql> SELECT "1998-01-01" - INTERVAL 1 SECOND;
                 -> 1997-12-31 23:59:59
          mysql> SELECT DATE_ADD("1997-12-31 23:59:59",
                                 INTERVAL 1 SECOND);
                  -> 1998-01-01 00:00:00
          mysql> SELECT DATE_ADD("1997-12-31 23:59:59",
                                 INTERVAL 1 DAY);
                  -> 1998-01-01 23:59:59
          mysql> SELECT DATE_ADD("1997-12-31 23:59:59",
                                 INTERVAL "1:1" MINUTE_SECOND);
                  -> 1998-01-01 00:01:00
          mysql> SELECT DATE_SUB("1998-01-01 00:00:00",
                                 INTERVAL "1 1:1:1" DAY_SECOND);
                  -> 1997-12-30 22:58:59
          mysql> SELECT DATE_ADD("1998-01-01 00:00:00",
                                 INTERVAL "-1 10" DAY_HOUR);
                  -> 1997-12-30 14:00:00
          mysql> SELECT DATE_SUB("1998-01-02", INTERVAL 31 DAY);
                  -> 1997-12-02
          mysql> SELECT EXTRACT(YEAR FROM "1999-07-02");
                 -> 1999
          mysql> SELECT EXTRACT(YEAR_MONTH FROM "1999-07-02 01:02:03");
                 -> 199907
          mysql> SELECT EXTRACT(DAY_MINUTE FROM "1999-07-02 01:02:03");
                 -> 20102

     If you specify an interval value that is too short (does not
     include all the interval parts that would be expected from the
     `type' keyword), *MySQL* assumes you have left out the leftmost
     parts of the interval value.  For example, if you specify a `type'
     of `DAY_SECOND', the value of `expr' is expected to have days,
     hours, minutes, and seconds parts.  If you specify a value like
     `"1:10"', *MySQL* assumes that the days and hours parts are
     missing and the value represents minutes and seconds.  In other
     words, `"1:10" DAY_SECOND' is interpreted in such a way that it is
     equivalent to `"1:10" MINUTE_SECOND'.  This is analogous to the
     way that *MySQL* interprets `TIME' values as representing elapsed
     time rather than as time of day.

     Note that if you add or subtract a date value against something
     that contains a time part, the date value will be automatically
     converted to a datetime value:

          mysql> select date_add("1999-01-01", interval 1 day);
                 -> 1999-01-02
          mysql> select date_add("1999-01-01", interval 1 hour);
                 -> 1999-01-01 01:00:00

     If you use really incorrect dates, the result is `NULL'. If you add
     `MONTH', `YEAR_MONTH', or `YEAR' and the resulting date has a day
     that is larger than the maximum day for the new month, the day is
     adjusted to the maximum days in the new month:

          mysql> select DATE_ADD('1998-01-30', Interval 1 month);
                  -> 1998-02-28

     Note from the preceding example that the word `INTERVAL' and the
     `type' keyword are not case sensitive.

`TO_DAYS(date)'
     Given a date `date', returns a daynumber (the number of days since
     year 0):

          mysql> select TO_DAYS(950501);
                  -> 728779
          mysql> select TO_DAYS('1997-10-07');
                  -> 729669

     `TO_DAYS()' is not intended for use with values that precede the
     advent of the Gregorian calendar (1582), because it doesn't take
     into account the days that were lost when the calender was changed.

`FROM_DAYS(N)'
     Given a daynumber `N', returns a `DATE' value:

          mysql> select FROM_DAYS(729669);
                  -> '1997-10-07'

     `FROM_DAYS()' is not intended for use with values that precede the
     advent of the Gregorian calendar (1582), because it doesn't take
     into account the days that were lost when the calender was changed.

`DATE_FORMAT(date,format)'
     Formats the `date' value according to the `format' string. The
     following specifiers may be used in the `format' string:
     `%M'    Month name (`January'..`December')
     `%W'    Weekday name (`Sunday'..`Saturday')
     `%D'    Day of the month with English suffix
             (`1st', `2nd', `3rd', etc.)
     `%Y'    Year, numeric, 4 digits
     `%y'    Year, numeric, 2 digits
     `%X'    Year for the week where Sunday is the
             first day of the week, numeric, 4
             digits, used with '%V'
     `%x'    Year for the week, where Monday is the
             first day of the week, numeric, 4
             digits, used with '%v'
     `%a'    Abbreviated weekday name (`Sun'..`Sat')
     `%d'    Day of the month, numeric (`00'..`31')
     `%e'    Day of the month, numeric (`0'..`31')
     `%m'    Month, numeric (`01'..`12')
     `%c'    Month, numeric (`1'..`12')
     `%b'    Abbreviated month name (`Jan'..`Dec')
     `%j'    Day of year (`001'..`366')
     `%H'    Hour (`00'..`23')
     `%k'    Hour (`0'..`23')
     `%h'    Hour (`01'..`12')
     `%I'    Hour (`01'..`12')
     `%l'    Hour (`1'..`12')
     `%i'    Minutes, numeric (`00'..`59')
     `%r'    Time, 12-hour (`hh:mm:ss [AP]M')
     `%T'    Time, 24-hour (`hh:mm:ss')
     `%S'    Seconds (`00'..`59')
     `%s'    Seconds (`00'..`59')
     `%p'    `AM' or `PM'
     `%w'    Day of the week
             (`0'=Sunday..`6'=Saturday)
     `%U'    Week (`0'..`53'), where Sunday is the
             first day of the week
     `%u'    Week (`0'..`53'), where Monday is the
             first day of the week
     `%V'    Week (`1'..`53'), where Sunday is the
             first day of the week. Used with '%X'
     `%v'    Week (`1'..`53'), where Monday is the
             first day of the week. Used with '%x'
     `%%'    A literal `%'.

     All other characters are just copied to the result without
     interpretation:

          mysql> select DATE_FORMAT('1997-10-04 22:23:00', '%W %M %Y');
                  -> 'Saturday October 1997'
          mysql> select DATE_FORMAT('1997-10-04 22:23:00', '%H:%i:%s');
                  -> '22:23:00'
          mysql> select DATE_FORMAT('1997-10-04 22:23:00',
                                    '%D %y %a %d %m %b %j');
                  -> '4th 97 Sat 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'

     As of *MySQL* Version 3.23, the `%' character is required before
     format specifier characters.  In earlier versions of *MySQL*, `%'
     was optional.

`TIME_FORMAT(time,format)'
     This is used like the `DATE_FORMAT()' function above, but the
     `format' string may contain only those format specifiers that
     handle hours, minutes, and seconds.  Other specifiers produce a
     `NULL' value or `0'.

`CURDATE()'
`CURRENT_DATE'
     Returns today's date as a value in `'YYYY-MM-DD'' or `YYYYMMDD'
     format, depending on whether the function is used in a string or
     numeric context:

          mysql> select CURDATE();
                  -> '1997-12-15'
          mysql> select CURDATE() + 0;
                  -> 19971215

`CURTIME()'
`CURRENT_TIME'
     Returns the current time as a value in `'HH:MM:SS'' or `HHMMSS'
     format, depending on whether the function is used in a string or
     numeric context:

          mysql> select CURTIME();
                  -> '23:50:26'
          mysql> select CURTIME() + 0;
                  -> 235026

`NOW()'
`SYSDATE()'
`CURRENT_TIMESTAMP'
     Returns the current date and time as a value in `'YYYY-MM-DD
     HH:MM:SS'' or `YYYYMMDDHHMMSS' format, depending on whether the
     function is used in a string or numeric context:

          mysql> select NOW();
                  -> '1997-12-15 23:50:26'
          mysql> select NOW() + 0;
                  -> 19971215235026

`UNIX_TIMESTAMP()'
`UNIX_TIMESTAMP(date)'
     If called with no argument, returns a Unix timestamp (seconds since
     `'1970-01-01 00:00:00'' GMT). If `UNIX_TIMESTAMP()' is called with
     a `date' argument, it returns the value of the argument as seconds
     since `'1970-01-01 00:00:00'' GMT.  `date' may be a `DATE' string,
     a `DATETIME' string, a `TIMESTAMP', or a number in the format
     `YYMMDD' or `YYYYMMDD' in local time:

          mysql> select UNIX_TIMESTAMP();
                  -> 882226357
          mysql> select UNIX_TIMESTAMP('1997-10-04 22:23:00');
                  -> 875996580

     When `UNIX_TIMESTAMP' is used on a `TIMESTAMP' column, the function
     will receive the value directly, with no implicit
     "string-to-unix-timestamp" conversion.  If you give
     `UNIX_TIMESTAMP()' a wrong or out-of-range date, it will return 0.

`FROM_UNIXTIME(unix_timestamp)'
     Returns a representation of the `unix_timestamp' argument as a
     value in `'YYYY-MM-DD HH:MM:SS'' or `YYYYMMDDHHMMSS' format,
     depending on whether the function is used in a string or numeric
     context:

          mysql> select FROM_UNIXTIME(875996580);
                  -> '1997-10-04 22:23:00'
          mysql> select FROM_UNIXTIME(875996580) + 0;
                  -> 19971004222300

`FROM_UNIXTIME(unix_timestamp,format)'
     Returns a string representation of the Unix timestamp, formatted
     according to the `format' string. `format' may contain the same
     specifiers as those listed in the entry for the `DATE_FORMAT()'
     function:

          mysql> select FROM_UNIXTIME(UNIX_TIMESTAMP(),
                                      '%Y %D %M %h:%i:%s %x');
                  -> '1997 23rd December 03:43:30 x'

`SEC_TO_TIME(seconds)'
     Returns the `seconds' argument, converted to hours, minutes, and
     seconds, as a value in `'HH:MM:SS'' or `HHMMSS' format, depending
     on whether the function is used in a string or numeric context:

          mysql> select SEC_TO_TIME(2378);
                  -> '00:39:38'
          mysql> select SEC_TO_TIME(2378) + 0;
                  -> 3938

`TIME_TO_SEC(time)'
     Returns the `time' argument, converted to seconds:

          mysql> select TIME_TO_SEC('22:23:00');
                  -> 80580
          mysql> select TIME_TO_SEC('00:39:38');
                  -> 2378