사용자 도구

사이트 도구


database:mysql:date

MySQL Date Functions

DATETIME 타입

  • MySQL 5.6 부터 DATETIME 타입에 대해 DEFAULT CURRENT_TIMESTAMP가 가능해졌다. 그 이하는 오류가 발생한다.
    `createdAt` datetime DEFAULT CURRENT_TIMESTAMP 가 있을 때,
    
    ERROR 1067 (42000): Invalid default value for 'createdAt'

date

  • 날짜/시간 값에서 날짜값만 남기기
  • 예 :
    select DATE(now()); -- 현재 날짜를 구함

date_add/adddate, date_sub/subdate

  • 날짜에 특정 값을 더하거나(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

str_to_date/date_format

  • 문자열을 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
database/mysql/date.txt · 마지막으로 수정됨: 2016/03/02 17:21 저자 kwon37xi