MySQL Date Functions

http://www.nazuni.pe.kr/web/dev/mysql/functions_datetime.php

MySQL 5.6 부터 DATETIME 타입에 대해 DEFAULT CURRENT_TIMESTAMP가 가능해졌다. 그 이하는 오류가 발생한다.

`createdAt` datetime DEFAULT CURRENT_TIMESTAMP 가 있을 때,

ERROR 1067 (42000): Invalid default value for 'createdAt'

날짜/시간 값에서 날짜값만 남기기

예 :

select DATE(now()); -- 현재 날짜를 구함

날짜에 특정 값을 더하거나(add) 빼기(sub)

DATE_ADD(date,INTERVAL expr type)

예 :

SELECT DATE_ADD(order_date,INTERVAL 45 DAY) from some_table;

기준 타입

MICROSECOND
SECOND
MINUTE
HOUR
DAY
WEEK
MONTH
QUARTER
YEAR
SECOND_MICROSECOND
MINUTE_MICROSECOND
MINUTE_SECOND
HOUR_MICROSECOND
HOUR_SECOND
HOUR_MINUTE
DAY_MICROSECOND
DAY_SECOND
DAY_MINUTE
DAY_HOUR
YEAR_MONTH

문자열을 DATETIME 으로 변경

STR_TO_DATE(str,format)

DATETIME을 문자열로 변경

DATE_FORMAT(date, format)

예 :

SELECT DATE_FORMAT(now(),'%Y-%m-%d'); -- 2012-12-27
SELECT STR_TO_DATE('2012-12-27 14:21:31','%Y-%m-%d %H:%i:%s');

Format

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