Funciones de Fechas y Tiempo
Interval
DATE_ADD ADDDATE
ADDTIME
CONVERT_TZ
NOW
CURDATE y CURRENT_DATE
CURTIME y CURRENT_TIME
DATE
DATEDIFF
DATE_FORMAT
DATE_SUB
DAYNAME
DAYOFMONTH
DAYOFWEEK
WEEKDAY
DAYOFYEAR

Funciones de Fechas y Tiempo

Interval

El directivo INTERVAL se puede utilizar para añadir o restar un 
intervalo de tiempo a un DATETIME, DATE o TIME valor.
La sintaxis es: 

INTERVAL time_quantity Time_unit

MariaDB [(none)]> SELECT '2015-10-21 12:15:30' +  INTERVAL 4 day;
+-----------------------------------------+
| '2015-10-21 12:15:30' +  INTERVAL 4 day |
+-----------------------------------------+
| 2015-10-25 12:15:30                     |
+-----------------------------------------+
1 row in set (0.01 sec)

MariaDB [(none)]> SELECT '2014-05-17 08:44:21' - INTERVAL '7 1' DAY_HOUR;
+-------------------------------------------------+
| '2014-05-17 08:44:21' - INTERVAL '7 1' DAY_HOUR |
+-------------------------------------------------+
| 2014-05-10 07:44:21                             |
+-------------------------------------------------+
1 row in set (0.00 sec)

Las siguientes unidades son válidas:

Undad	Descripción
`MICROSECOND`	Microseconds
`SECOND`	Seconds
`MINUTE`	Minutes
`HOUR`	Hours
`DAY`	Days
`WEEK`	Weeks
`MONTH`	Months
`QUARTER`	Quarters
`YEAR`	Years
`SECOND_MICROSECOND`	Seconds.Microseconds
`MINUTE_MICROSECOND`	Minutes.Seconds.Microseconds
`MINUTE_SECOND`	Minutes.Seconds
`HOUR_MICROSECOND`	Hours.Minutes.Seconds.Microseconds
`HOUR_SECOND`	Hours.Minutes.Seconds
`HOUR_MINUTE`	Hours.Minutes
`DAY_MICROSECOND`	Days Hours.Minutes.Seconds.Microseconds
`DAY_SECOND`	Days Hours.Minutes.Seconds
`DAY_MINUTE`	Days Hours.Minutes
`DAY_HOUR`	Days Hours
`YEAR_MONTH`	Years-Months

DATE_ADD ADDDATE

Cuando se invoca con la forma INTERVAL del segundo argumento, ADDDATE () 
es sinónimo de DATE_ADD (). Cuando se invoca con la forma DAY del segundo 
argumento, MariaDB lo trata como un número entero de días a añadir a expr.

MariaDB [(none)]> SELECT DATE_ADD('2015-10-25', INTERVAL 31 DAY);
+-----------------------------------------+
| DATE_ADD('2015-10-25', INTERVAL 31 DAY) |
+-----------------------------------------+
| 2015-11-25                              |
+-----------------------------------------+
1 row in set (0.00 sec)

MariaDB [(none)]> SELECT ADDDATE('2015-10-25', INTERVAL 31 DAY);
+----------------------------------------+
| ADDDATE('2015-10-25', INTERVAL 31 DAY) |
+----------------------------------------+
| 2015-11-25                             |
+----------------------------------------+
1 row in set (0.00 sec)

MariaDB [(none)]> SELECT ADDDATE('2015-10-25', 31);
+---------------------------+
| ADDDATE('2015-10-25', 31) |
+---------------------------+
| 2015-11-25                |
+---------------------------+
1 row in set (0.00 sec)

MariaDB [(none)]> use Prueba1
Database changed

MariaDB [Prueba1]> CREATE TABLE t1 (d DATETIME);
Query OK, 0 rows affected (0.25 sec)

MariaDB [Prueba1]> INSERT INTO t1 VALUES
    ->     ("2007-01-30 21:31:07"),
    ->     ("1983-10-15 06:42:51"),
    ->     ("2011-04-21 12:34:56"),
    ->     ("2011-10-30 06:31:41"),
    ->     ("2011-01-30 14:03:25"),
    ->     ("2004-10-07 11:19:34");
Query OK, 6 rows affected (0.08 sec)
Records: 6  Duplicates: 0  Warnings: 0

MariaDB [Prueba1]> SELECT d, ADDDATE(d, 10) from t1;
+---------------------+---------------------+
| d                   | ADDDATE(d, 10)      |
+---------------------+---------------------+
| 2007-01-30 21:31:07 | 2007-02-09 21:31:07 |
| 1983-10-15 06:42:51 | 1983-10-25 06:42:51 |
| 2011-04-21 12:34:56 | 2011-05-01 12:34:56 |
| 2011-10-30 06:31:41 | 2011-11-09 06:31:41 |
| 2011-01-30 14:03:25 | 2011-02-09 14:03:25 |
| 2004-10-07 11:19:34 | 2004-10-17 11:19:34 |
+---------------------+---------------------+
6 rows in set (0.01 sec)

MariaDB [Prueba1]> SELECT d, ADDDATE(d, INTERVAL 10 HOUR) from t1;
+---------------------+------------------------------+
| d                   | ADDDATE(d, INTERVAL 10 HOUR) |
+---------------------+------------------------------+
| 2007-01-30 21:31:07 | 2007-01-31 07:31:07          |
| 1983-10-15 06:42:51 | 1983-10-15 16:42:51          |
| 2011-04-21 12:34:56 | 2011-04-21 22:34:56          |
| 2011-10-30 06:31:41 | 2011-10-30 16:31:41          |
| 2011-01-30 14:03:25 | 2011-01-31 00:03:25          |
| 2004-10-07 11:19:34 | 2004-10-07 21:19:34          |
+---------------------+------------------------------+
6 rows in set (0.00 sec)

MariaDB [Prueba1]> SELECT DATE_ADD('2015-12-31 23:59:59', INTERVAL 1 SECOND);
+----------------------------------------------------+
| DATE_ADD('2015-12-31 23:59:59', INTERVAL 1 SECOND) |
+----------------------------------------------------+
| 2016-01-01 00:00:00                                |
+----------------------------------------------------+
1 row in set (0.00 sec)

MariaDB [Prueba1]> SELECT DATE_ADD('1900-01-01 00:00:00', INTERVAL '-1 10' DAY_HOUR);
+------------------------------------------------------------+
| DATE_ADD('1900-01-01 00:00:00', INTERVAL '-1 10' DAY_HOUR) |
+------------------------------------------------------------+
| 1899-12-30 14:00:00                                        |
+------------------------------------------------------------+
1 row in set (0.00 sec)

ADDTIME

ADDTIME(expr1,expr2)

ADDTIME () añade expr2 a expr1 y devuelve el resultado. 
expr1 es una expresión de tiempo o de fecha y hora, y 
expr2 es una expresión de tiempo.

MariaDB [Prueba1]> SELECT ADDTIME('2007-12-31 23:59:59.999999', '1 1:1:1.000002');
+---------------------------------------------------------+
| ADDTIME('2007-12-31 23:59:59.999999', '1 1:1:1.000002') |
+---------------------------------------------------------+
| 2008-01-02 01:01:01.000001                              |
+---------------------------------------------------------+
1 row in set (0.00 sec)

MariaDB [Prueba1]> SELECT ADDTIME('01:00:00.999999', '02:00:00.999998');
+-----------------------------------------------+
| ADDTIME('01:00:00.999999', '02:00:00.999998') |
+-----------------------------------------------+
| 03:00:01.999997                               |
+-----------------------------------------------+
1 row in set (0.00 sec)

CONVERT_TZ

CONVERT_TZ(dt,from_tz,to_tz)

CONVERT_TZ () convierte un valor datetime dt de la zona de tiempo dado por from_tz 
a la zona horaria dada por to_tz y devuelve el valor resultante. Esta función 
devuelve NULL si los argumentos no son válidos.

MariaDB [Prueba1]> SELECT CONVERT_TZ('2004-01-01 12:00:00','+00:00','+10:00');
+-----------------------------------------------------+
| CONVERT_TZ('2004-01-01 12:00:00','+00:00','+10:00') |
+-----------------------------------------------------+
| 2004-01-01 22:00:00                                 |
+-----------------------------------------------------+
1 row in set (0.00 sec)

MariaDB [Prueba1]> SELECT CONVERT_TZ('2004-01-01 12:00:00','+00:00','CDT');
+--------------------------------------------------+
| CONVERT_TZ('2004-01-01 12:00:00','+00:00','CDT') |
+--------------------------------------------------+
| NULL                                             |
+--------------------------------------------------+
1 row in set (0.00 sec)

MariaDB [Prueba1]> SELECT CONVERT_TZ('2004-01-01 12:00:00','+00:00','+CDT');
+---------------------------------------------------+
| CONVERT_TZ('2004-01-01 12:00:00','+00:00','+CDT') |
+---------------------------------------------------+
| NULL                                              |
+---------------------------------------------------+
1 row in set (0.00 sec)

MariaDB [Prueba1]> SELECT CONVERT_TZ('2004-01-01 12:00:00','+00:00','CST');
+--------------------------------------------------+
| CONVERT_TZ('2004-01-01 12:00:00','+00:00','CST') |
+--------------------------------------------------+
| NULL                                             |
+--------------------------------------------------+
1 row in set (0.00 sec)

MariaDB [Prueba1]> SELECT CONVERT_TZ('2004-01-01 12:00:00','-6:00','+4:00');
+---------------------------------------------------+
| CONVERT_TZ('2004-01-01 12:00:00','-6:00','+4:00') |
+---------------------------------------------------+
| 2004-01-01 22:00:00                               |
+---------------------------------------------------+
1 row in set (0.00 sec)

NOW

NOW(), CURRENT_TIMESTAMP, CURRENT_TIMESTAMP(),
LOCALTIME, LOCALTIME(), LOCALTIMESTAMP, LOCALTIMESTAMP()

Devuelve la fecha y la hora actual como valor en formato 'YYYY-MM-DD HH: MM: SS' 
o el formato YYYYMMDDHHMMSS.uuuuuu, dependiendo de si la función se utiliza en 
una cadena o contexto numérico. El valor se expresa en la zona horaria actual.

NOW () (o sus sinónimos) se puede usar como el valor predeterminado para columnas 
TIMESTAMP, así como, desde MariaDB 10.0.1, columnas DATETIME. Antes MariaDB 10,0.1, 
sólo era posible que una sola columna TIMESTAMP por mesa para contener 
la CURRENT_TIMESTAMP como su defecto.

LOCALTIME and LOCALTIME() son sinónimos para NOW().
LOCALTIMESTAMP and LOCALTIMESTAMP() son sinónimos para NOW().
CURRENT_TIMESTAMP and CURRENT_TIMESTAMP() son sinónimos para NOW().

MariaDB [(none)]> SELECT NOW();
+---------------------+
| NOW()               |
+---------------------+
| 2015-10-27 18:07:00 |
+---------------------+
1 row in set (0.01 sec)

MariaDB [(none)]> SELECT NOW() + 0;
+----------------+
| NOW() + 0      |
+----------------+
| 20151027180709 |
+----------------+
1 row in set (0.02 sec)

CURDATE

CURDATE()

Devuelve la fecha actual como valor en formato 'YYYY-MM-DD' 
o el formato AAAAMMDD, dependiendo de si la función se utiliza 
en una cadena o contexto numérico.

CURRENT_DATE y CURRENT_DATE () son sinónimos de CURDATE ().

MariaDB [Prueba1]> SELECT CURDATE();
+------------+
| CURDATE()  |
+------------+
| 2015-10-26 |
+------------+
1 row in set (0.00 sec)

MariaDB [Prueba1]> SELECT CURDATE() + 50;
+----------------+
| CURDATE() + 50 |
+----------------+
|       20151076 |
+----------------+
1 row in set (0.00 sec)

CURTIME

CURTIME()

Devuelve la hora actual como un valor en 'HH: MM: SS' o el formato HHMMSS.uuuuuu, 
dependiendo de si la función se utiliza en una cadena o contexto numérico. 
El valor se expresa en la zona horaria actual.

CURRENT_TIME y CURRENT_TIME () son sinónimos de CURTIME ().

MariaDB [Prueba1]> SELECT CURTIME();
+-----------+
| CURTIME() |
+-----------+
| 23:36:26  |
+-----------+
1 row in set (0.00 sec)

MariaDB [Prueba1]> SELECT CURTIME() + 55;
+----------------+
| CURTIME() + 55 |
+----------------+
|         233693 |
+----------------+
1 row in set (0.00 sec)

DATE

DATE( expr )

Extrae la parte de fecha de la fecha o la fecha y hora expresión expr.

Hasta MariaDB 05/05/32, algunas versiones de MariaDB regresaron 0000-00-00 
cuando se pasa una fecha no válida. Desde 05/05/32, NULL se devuelve.

MariaDB [Prueba1]> SELECT DATE('2015-10-25 12:21:32');
+-----------------------------+
| DATE('2015-10-25 12:21:32') |
+-----------------------------+
| 2015-10-25                  |
+-----------------------------+
1 row in set (0.00 sec)

DATEDIFF

DATEDIFF(expr1,expr2)

DATEDIFF () devuelve (Expr1 - expr2) expresados como un valor en días 
de una fecha a la otra. expr1 y expr2 son de fecha o de expresiones DATE y tiempo 
de expresiones. Sólo las partes de fecha de los valores se utilizan en el cálculo.

MariaDB [Prueba1]> SELECT DATEDIFF('2007-12-31 23:59:59','2007-12-30');
+----------------------------------------------+
| DATEDIFF('2007-12-31 23:59:59','2007-12-30') |
+----------------------------------------------+
|                                            1 |
+----------------------------------------------+
1 row in set (0.00 sec)

MariaDB [Prueba1]> SELECT DATEDIFF('2010-11-30 23:59:59','2010-12-31');
+----------------------------------------------+
| DATEDIFF('2010-11-30 23:59:59','2010-12-31') |
+----------------------------------------------+
|                                          -31 |
+----------------------------------------------+
1 row in set (0.00 sec)

MariaDB [Prueba1]> CREATE TABLE t2 (d DATETIME);
Query OK, 0 rows affected (0.23 sec)

MariaDB [Prueba1]> INSERT INTO t2 values
           ("2007-01-30 21:31:07"),
           ("1983-10-15 06:42:51"),
           ("2011-04-21 12:34:56"),
           ("2011-10-30 06:31:41"),
           ("2011-01-30 14:03:25"),
           ("2004-10-07 11:19:34");
Query OK, 6 rows affected (0.04 sec)
Records: 6  Duplicates: 0  Warnings: 0

MariaDB [Prueba1]> SELECT NOW();
+---------------------+
| NOW()               |
+---------------------+
| 2015-10-26 23:59:32 |
+---------------------+
1 row in set (0.00 sec)

MariaDB [Prueba1]> SELECT d, DATEDIFF(NOW(),d) FROM t2;
+---------------------+-------------------+
| d                   | DATEDIFF(NOW(),d) |
+---------------------+-------------------+
| 2007-01-30 21:31:07 |              3191 |
| 1983-10-15 06:42:51 |             11699 |
| 2011-04-21 12:34:56 |              1649 |
| 2011-10-30 06:31:41 |              1457 |
| 2011-01-30 14:03:25 |              1730 |
| 2004-10-07 11:19:34 |              4036 |
+---------------------+-------------------+
6 rows in set (0.00 sec)

DATE_FORMAT

DATE_FORMAT(date,format)

Formatea el valor de DATE de acuerdo a la cadena de formato. 
El lenguaje utilizado para los nombres es controlado por el valor 
de la variable de sistema lc_time_names. Ver la configuración 
regional del servidor para más información sobre los entornos 
locales soportados. Las opciones que se pueden utilizar por 
DATE_FORMAT(), así como su inversa STR_TO_DATE() y el 

MariaDB [Prueba1]> SELECT DATE_FORMAT('2009-10-04 22:23:00', '%W %M %Y');
+------------------------------------------------+
| DATE_FORMAT('2009-10-04 22:23:00', '%W %M %Y') |
+------------------------------------------------+
| Sunday October 2009                            |
+------------------------------------------------+
1 row in set (0.00 sec)

MariaDB [Prueba1]> SELECT DATE_FORMAT('2007-10-04 22:23:00', '%H:%i:%s');
+------------------------------------------------+
| DATE_FORMAT('2007-10-04 22:23:00', '%H:%i:%s') |
+------------------------------------------------+
| 22:23:00                                       |
+------------------------------------------------+
1 row in set (0.00 sec)

MariaDB [Prueba1]> SELECT DATE_FORMAT('1900-10-04 22:23:00', '%D %y %a %d %m %b %j');
+------------------------------------------------------------+
| DATE_FORMAT('1900-10-04 22:23:00', '%D %y %a %d %m %b %j') |
+------------------------------------------------------------+
| 4th 00 Thu 04 10 Oct 277                                   |
+------------------------------------------------------------+
1 row in set (0.00 sec)

MariaDB [Prueba1]> SELECT DATE_FORMAT('1997-10-04 22:23:00', '%H %k %I %r %T %S %w');
+------------------------------------------------------------+
| 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                         |
+------------------------------------------------------------+
1 row in set (0.00 sec)

MariaDB [Prueba1]> SELECT DATE_FORMAT('1999-01-01', '%X %V');
+------------------------------------+
| DATE_FORMAT('1999-01-01', '%X %V') |
+------------------------------------+
| 1998 52                            |
+------------------------------------+
1 row in set (0.00 sec)

MariaDB [Prueba1]> SELECT DATE_FORMAT('2006-06-00', '%d');
+---------------------------------+
| DATE_FORMAT('2006-06-00', '%d') |
+---------------------------------+
| 00                              |
+---------------------------------+
1 row in set (0.00 sec)

lc_time_names

La configuración regional que determina el idioma utilizado para la fecha 
y hora funciones DAYNAME (), MONTHNAME () y DATE_FORMAT (). Nombres Locale 
son de idioma y región subetiquetas, por ejemplo 'en_ZA' (Inglés - Sudáfrica) 
o 'es_US: Español - Estados Unidos'. El valor por defecto es siempre "en-US",
independientemente de la configuración regional del sistema. Ver la 
configuración regional del servidor para obtener una lista completa de 
los entornos locales soportados. 

FROM_UNIXTIME() función, son los siguientes:

Option	Description
`%D`	Day with English suffix 'th', 'nd', 'st' or 'rd. (1st, 2nd, 3rd...).
`%H`	Hour with 2 digits between 00-23.
`%I`	Hour with 2 digits between 01-12.
`%M`	Full month name in current locale (Variable lc_time_names).
`%S`	Seconds with 2 digits.
`%T`	Time in 24 hour format. Short for '%H:%i:%S'.
`%U`	Week number (00-53), when first day of the week is Sunday.
`%V`	Week number (01-53), when first day of the week is Sunday. Used with %X.
`%W`	Full weekday name in current locale (Variable lc_time_names).
`%X`	Year with 4 digits when first day of the week is Sunday. Used with %V.
`%Y`	Year with 4 digits.
`%a`	Short weekday name in current locale (Variable lc_time_names).
`%b`	Short form month name in current locale. For locale en_US this is one of: Jan,Feb,Mar,Apr,May,Jun,Jul,Aug,Sep,Oct,Nov or Dec.
`%c`	Month with 1 or 2 digits.
`%d`	Day with 2 digits.
`%e`	Day with 1 or 2 digits.
`%f`	Sub seconds 6 digits.
`%h`	Hour with 2 digits between 01-12.
`%i`	Minute with 2 digits
`%j`	Day of the year (001-366)
`%k`	Hour with 1 digits between 0-23.
`%l`	Hour with 1 digits between 1-12.
`%m`	Month with 2 digits.
`%p`	AM/PM according to current locale (Variable lc_time_names).
`%r`	Time in 12 hour format, followed by AM/PM. Short for '%I:%i:%S %p'.
`%s`	Seconds with 2 digits.
`%u`	Week number (00-53), when first day of the week is Monday.
`%v`	Week number (01-53), when first day of the week is Monday. Used with %x.
`%w`	Day of the week. 0 = Sunday, 1 = Saturday.
`%x`	Year with 4 digits when first day of the week is Sunday. Used with %v.
`%y`	Year with 2 digits.
`#`	For str_to_date(), skip all numbers.
`.`	For str_to_date(), skip all punctation characters.
`@`	For str_to_date(), skip all alpha characters.
`%%`	A literal `%` character.

DATE_SUB

DATE_SUB(date,INTERVAL expr unit)

Realiza fecha aritmética. El argumento date especifica la fecha o 
la fecha y hora valor inicial. expr es una expresión que especifica 
el valor de intervalo a añadir o restar de la fecha de inicio. 
expr es una cadena; puede comenzar con un "-" para intervalos 
negativos. unit es un operador que indica las unidades en las que 
la expresión debe interpretarse. 

MariaDB [(none)]> SELECT DATE_SUB('2015-10-28', INTERVAL 31 DAY);
+-----------------------------------------+
| DATE_SUB('2015-10-28', INTERVAL 31 DAY) |
+-----------------------------------------+
| 2015-09-27                              |
+-----------------------------------------+
1 row in set (0.06 sec)

DAYNAME

DAYNAME(date)

Devuelve el nombre del día de la semana de la fecha. El lenguaje utilizado 
para el nombre es controlado por el valor de la variable de sistema
lc_time_names.

MariaDB [(none)]> SELECT DAYNAME('1951-09-04');
+-----------------------+
| DAYNAME('1951-09-04') |
+-----------------------+
| Tuesday               |
+-----------------------+
1 row in set (0.03 sec)

MariaDB [(none)]> SELECT DAYNAME('1951-09-04');
+-----------------------+
| DAYNAME('1951-09-04') |
+-----------------------+
| Tuesday               |
+-----------------------+
1 row in set (0.03 sec)

MariaDB [(none)]> use Prueba1;
Database changed

MariaDB [Prueba1]> CREATE TABLE t3 (d DATETIME);
Query OK, 0 rows affected (0.48 sec)

MariaDB [Prueba1]> INSERT INTO t3 VALUES
           ("2007-01-30 21:31:07"),
           ("1983-10-15 06:42:51"),
           ("2011-04-21 12:34:56"),
           ("2011-10-30 06:31:41"),
           ("2011-01-30 14:03:25"),
           ("2004-10-07 11:19:34");
Query OK, 6 rows affected (0.07 sec)
Records: 6  Duplicates: 0  Warnings: 0

MariaDB [Prueba1]> SET lc_time_names = 'es_MX';
Query OK, 0 rows affected (0.02 sec)

MariaDB [Prueba1]> SELECT d, DAYNAME(d) FROM t3;
+---------------------+------------+
| d                   | DAYNAME(d) |
+---------------------+------------+
| 2007-01-30 21:31:07 | martes     |
| 1983-10-15 06:42:51 | sábado     |
| 2011-04-21 12:34:56 | jueves     |
| 2011-10-30 06:31:41 | domingo    |
| 2011-01-30 14:03:25 | domingo    |
| 2004-10-07 11:19:34 | jueves     |
+---------------------+------------+
6 rows in set (0.00 sec)

DAYOFMONTH

DAYOFMONTH(date)

Devuelve el día del mes para la fecha, en el rango de 1 a 31, 
o 0 para fechas como '0000-00-00' o '2008-00-00', que tiene 
una parte de cero días.

DAY() es sinónimo para DAYOFMONTH().

MariaDB [Prueba1]> SELECT DAYOFMONTH('2007-02-03');
+--------------------------+
| DAYOFMONTH('2007-02-03') |
+--------------------------+
|                        3 |
+--------------------------+
1 row in set (0.00 sec)

MariaDB [Prueba1]> CREATE TABLE t4 (d DATETIME);
Query OK, 0 rows affected (0.31 sec)

MariaDB [Prueba1]> INSERT INTO t4 values
    -> ("2007-01-30 21:31:07"),
    ->     ("1983-10-15 06:42:51"),
    ->     ("2011-04-21 12:34:56"),
    ->     ("2011-10-30 06:31:41"),
    ->     ("2011-01-30 14:03:25"),
    ->     ("2004-10-07 11:19:34");
Query OK, 6 rows affected (0.11 sec)
Records: 6  Duplicates: 0  Warnings: 0

MariaDB [Prueba1]> SELECT d FROM t4 where DAYOFMONTH(d) = 30;
+---------------------+
| d                   |
+---------------------+
| 2007-01-30 21:31:07 |
| 2011-10-30 06:31:41 |
| 2011-01-30 14:03:25 |
+---------------------+
3 rows in set (0.00 sec)

</syntaxhigh>

== DAYOFWEEK ==

<syntaxhighlight lang="sql" line="GESHI_FANCY_LINE_NUMBERS">

DAYOFWEEK(date)

Devuelve el indice del día de la semana para la fecha 
(1 = Domingo, 2 = Lunes, ..., 7 = Sábado). Estos valores de índice 
se corresponden con el estándar ODBC. 
Esto contrasta con WEEKDAY() que sigue una numeración índice diferente 
(0 = Lunes, 1 = martes ... 6 = domingo).

MariaDB [Prueba1]> SELECT DAYOFWEEK('2007-02-03');
+-------------------------+
| DAYOFWEEK('2007-02-03') |
+-------------------------+
|                       7 |
+-------------------------+
1 row in set (0.00 sec)

MariaDB [Prueba1]> CREATE TABLE t5 (d DATETIME);
Query OK, 0 rows affected (0.39 sec)

MariaDB [Prueba1]> INSERT INTO t5 VALUES
    -> 
    -> ("2007-01-30 21:31:07"),
    ->     ("1983-10-15 06:42:51"),
    ->     ("2011-04-21 12:34:56"),
    ->     ("2011-10-30 06:31:41"),
    ->     ("2011-01-30 14:03:25"),
    ->     ("2004-10-07 11:19:34");
Query OK, 6 rows affected (0.06 sec)
Records: 6  Duplicates: 0  Warnings: 0

MariaDB [Prueba1]> SELECT d, DAYNAME(d), DAYOFWEEK(d), WEEKDAY(d) from t5;
+---------------------+------------+--------------+------------+
| d                   | DAYNAME(d) | DAYOFWEEK(d) | WEEKDAY(d) |
+---------------------+------------+--------------+------------+
| 2007-01-30 21:31:07 | martes     |            3 |          1 |
| 1983-10-15 06:42:51 | sábado     |            7 |          5 |
| 2011-04-21 12:34:56 | jueves     |            5 |          3 |
| 2011-10-30 06:31:41 | domingo    |            1 |          6 |
| 2011-01-30 14:03:25 | domingo    |            1 |          6 |
| 2004-10-07 11:19:34 | jueves     |            5 |          3 |
+---------------------+------------+--------------+------------+
6 rows in set (0.00 sec)

WEEKDAY

WEEKDAY(date)
Devuelve el índice de lunes a viernes por la fecha 
(0 = Lunes, 1 = martes ... 6 = domingo). 
Esto contrasta con DAYOFWEEK() que sigue el estándar ODBC 
(1 = domingo, 2 = Lunes, ..., 7 = sábado).

MariaDB [Prueba1]> SELECT WEEKDAY('2015-10-30 22:23:00');
+--------------------------------+
| WEEKDAY('2015-10-30 22:23:00') |
+--------------------------------+
|                              4 |
+--------------------------------+
1 row in set (0.00 sec)

MariaDB [Prueba1]> SELECT WEEKDAY('2015-11-01');
+-----------------------+
| WEEKDAY('2015-11-01') |
+-----------------------+
|                     6 |
+-----------------------+
1 row in set (0.00 sec)

MariaDB [Prueba1]> CREATE TABLE t6 (d DATETIME);
Query OK, 0 rows affected (0.29 sec)

MariaDB [Prueba1]> INSERT INTO t6 VALUES
    ->  ("2007-01-30 21:31:07"),
    ->     ("1983-10-15 06:42:51"),
    ->     ("2011-04-21 12:34:56"),
    ->     ("2011-10-30 06:31:41"),
    ->     ("2011-01-30 14:03:25"),
    ->     ("2004-10-07 11:19:34");
Query OK, 6 rows affected (0.11 sec)
Records: 6  Duplicates: 0  Warnings: 0

MariaDB [Prueba1]> SELECT d FROM t6 where WEEKDAY(d) = 6;
+---------------------+
| d                   |
+---------------------+
| 2011-10-30 06:31:41 |
| 2011-01-30 14:03:25 |
+---------------------+
2 rows in set (0.00 sec)

DAYOFYEAR

DAYOFYEAR(date)

Devuelve el día del año para la fecha, en el rango de 1-366.

MariaDB [Prueba1]> SELECT DAYOFYEAR('2015-10-30');
+-------------------------+
| DAYOFYEAR('2015-10-30') |
+-------------------------+
|                     303 |
+-------------------------+
1 row in set (0.00 sec)

Curso de MariaDB Desde la Consola/Funciones de Fechas y Tiempo

Funciones de Fechas y Tiempo

Interval

DATE_ADD ADDDATE

ADDTIME

CONVERT_TZ

NOW

CURDATE

CURTIME

DATE

DATEDIFF

DATE_FORMAT

DATE_SUB

DAYNAME

DAYOFMONTH

WEEKDAY

DAYOFYEAR

Menú de navegación

Herramientas personales

Espacios de nombres

Variantes

Vistas

Más

Buscar

WikiCabal

Navegación

Herramientas