웹이야기

MySQL 날짜함수, 시간함수 YEARWEEK() - YEARWEEK(date), YEARWEEK(date, mode) 의 형태 Returns year and week for a date. The year in the result may be different form the year in the date argument for the first and the last week of the year. The mode argument works exactll like the mode argument to WEEK(). For the single-argument syntax, a mode value of 0 is used. Unlike WEEK(), the value of default_week_format..

MySQL 날짜함수, 시간함수 YEAR() - YEAR(date) 의 형태 Returns the year for date, in the range 1000 to 9999, or 0 for the "zero" date. mysql> SELECT YEAR(NOW()); +-------------+ | YEAR(NOW()) | +-------------+ | 2020 | +-------------+ 1 row in set (0.00 sec) mysql> SELECT YEAR('2020-04-01'); +--------------------+ | YEAR('2020-04-01') | +--------------------+ | 2020 | +--------------------+ 1 row in set (0.0..

MySQL 날짜함수, 시간함수 WEEKOFYEAR() - WEEKOFYEAR(date) 의 형태 Returns the calendar week of the date as a number in the range from 1 to 53. WEEKOFYEAR() is a compatibility function that is equivalent to WEEK(date, 3). mysql> SELECT WEEKOFYEAR(NOW()); +-------------------+ | WEEKOFYEAR(NOW()) | +-------------------+ | 14 | +-------------------+ 1 row in set (0.00 sec) mysql> SELECT WEEKOFYEAR('2020-04-05'..

MySQL 날짜함수, 시간함수 WEEKDAY() - WEEKDAY([date]) 의 형태 Returns the weekday index for date(0=Monday, 1=Tuesday, ... 6=Sunday) mysql> SELECT WEEKDAY(NOW()); +----------------+ | WEEKDAY(NOW()) | +----------------+ | 6 | +----------------+ 1 row in set (0.00 sec) mysql> SELECT WEEKDAY('2020-04-16'); +-----------------------+ | WEEKDAY('2020-04-16') | +-----------------------+ | 3 | +--------------------..

MySQL 날짜함수, 시간함수 WEEK() - WEEK(date[, mode]) 의 형태 This function returns the week number for date. The two-argument form of WEEK() enables you to specify whether the week starts on Sunday or Monday and whether the return value should be in the range from 0 to 53 or 1 to 53. If the mode argument is omitted, the value of the default_week_format system variable is used. The following table describes..

MySQL 날짜함수, 시간함수 UNIX_TIMESTAMP() - UNIX_TIMESTAMP([date]) 의 형태 If UNIX_TIMESTAMP() is called with no date argument, it returns a Unix timestamp representing seconds since '1970-01-01 00:00:00' UTC. If UNIX_TIMESTAMP() is called with a date argument, it returns the value of the argument as seconds sincd '1970-01-01 00:00:00' UTC. The server interprets date as a value in the session time zone and..

MySQL 날짜함수, 시간함수 TO_SECONDS() - TO_SECONDS(expr) 의 형태 Given a date or datetime expr, returns the number of seconds since the year 0. If expr is not a valid date or datetime value, returns NULL. mysql> SELECT TO_SECONDS(NOW()); +-------------------+ | TO_SECONDS(NOW()) | +-------------------+ | 63753308673 | +-------------------+ 1 row in set (0.00 sec) mysql> SELECT TO_SECONDS(950501); +--------..

MySQL 날짜함수, 시간함수 TO_DAYS() - TO_DAYS(date) 의 형태 Given a date date, returns a day number(the number of days since year 0) TO_DAYS() is not intended for use with values that precede the advent of the Gregorian calendar(1582), because it does not take into account the days that were lost when the calendar was changed. For dates before 1582(and possibly a later year in other locales), results from t..

MySQL 날짜함수, 시간함수 TIME_FORMAT() - TIME_FORMAT(time format) 의 형태 This is used like the DATE_FORMAT() function, but the format string may contain format specifiers only for hours, minute, seconds, and microseconds. Other specifiers produce a NULL value or 0. It the time value contains an hour part that is greater than 23, the %H and %k hour format specifiers produce a value larger than the usual ra..

MySQL 날짜함수, 시간함수 TIMESTAMPDIFF() - TIMESTAMPDIFF(unit, datetime_expr1, datetime_expr2) 의 형태 Returns datetime_expr2 - datetime_expr1, where datetime_expr1 and datetime_expr2 are date or datetime expressions. One expression may be a date and the other a datetime: a date value is treated as a datetime having the time part '00:00:00' where necessary. The unit for the result (an integer) is given by ..

MySQL 날짜함수, 시간함수 TIMESTAMPADD() - TIMESTAMPADD(unit, interval, datetime_expr) 의 형태 Adds the integer expression interval to the date or datetime expression datetime_expr. The unit for interval is given by the unit argument, which should be one of the following values: MICROSECOND(microseconds), SECOND, MINUTE, HOUR, DAY, WEEK, MONTH, QUARTER, or YEAR. The unit value may be specified unsing on of ..

MySQL 날짜함수, 시간함수 TIMESTAMP() - argument에 대한 datetime 값을 return, argument가 2개인경우는 더해서 datetime 값을 return. TIMESTAMP(expr), TIMESTAMP(expr1, expr2) 의 형태 With a single argument, this function returns the date or datetime expression expr as a datetime value. With two arguments, it adds the time expression expr2 to the date or datetime expression expr1 and return the result as a datetime value. mysql..

MySQL 날짜함수, 시간함수 TIMEDIFF() - 시간의 차이를 구해주는 함수. TIMEDIFF(expr1, expr2) 의 형태 TIMEDIFF() returns expr1 - expr2 expressed a s a TIME value. expr1 and expr2 are time or date-and-time expressions, but both must be of the same type. The resuilt returned by TIMEDIFF() is limited to the range allowed for TIME vlaues. Alternatively , you can use either of the functions TIMESTAMPDIFF() and UNIX_TIMESTAMP()..

MySQL 날짜함수, 시간함수 SYSDATE() - 현재시간을 구해주는 함수. NOW()와 비슷하지만 다르다. 내 패턴으론 이 함수가 좀 더 어울리는 것 같다. SYSDATE([fsp]) 의 형태 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 string or numeric context. If the fsp argument is given to sepcifiy a fractional seconds precision form 0 to 6, the return value includes a fractio..

MySQL 날짜함수, 시간함수 SUBTIME() - 시간을 빼는 함수 SUBTIME(expr1, expr2) 의 형태 SUBTIME() returns expr1 - expr2 expressed as a value in the same format as expr1. expr1 is a time or datetime expression, and expr2 is a time expression. mysql> SELECT SUBTIME('2007-12-31 23:59:59', '1 1:1:1.000002'); +--------------------------------------------------+ | SUBTIME('2007-12-31 23:59:59', '1 1:1:1.000002') | +-------..

MySQL 날짜함수, 시간함수 SUBDATE() - 날짜를 빼줄 수 있는 함수, 사용방법은 2가지. - DATE_ADD(), DATE_SUB()와 유사. - 날짜를 뺄 때 -도 가능하므로 날짜 더하기 빼기 모두 가능하다. SUBDATE(date, INTERVAL expr unit) SUBDATE(expr, days) 의 형태 When invoked with the INTERVAL form of the second argument, SUBDATE() is a synonym for DATE_SUB(). For information on the INTERVAL unit argumet. see the disussion for DATE_ADD(). https://webobj.tistory.com/114 MySQL..

MySQL 날짜함수, 시간함수 STR_TO_DATE() - DATE_FORMAT() 의 반대 - 문자열을 날짜형태로 바꾸어준다. (format에 주의) STR_TO_DATE(str, format) 의 형태 This is the inverse of the DATE_FORMAT() function. It takes a string str and a format string format. STR_TO_DATE() returns a DATETIME value if the format string contains both date and time parts, or a DATE or TIME value if the string contains only date or time parts. If the date, ti..

MySQL 날짜함수, 시간함수 SEC_TO_TIME() - 초를 시간으로 변환 SEC_TO_TIME(seconds) 의 형태 Returns the seconds argument, converted to hours, minutes, and seconds, as a TIME value. The range of the result is constrained to that of the TIME data type. A warning occurs if the argument corresponds to a value outside that range. mysql> SELECT SEC_TO_TIME(2378); +-------------------+ | SEC_TO_TIME(2378) | +---------------..

MySQL 날짜함수, 시간함수 QUARTER() - 날짜를 이용해서 분기를 구해주는 함수 QUARTER(date) 의 형태 Returns the quarter of the year for date, in the range 1 to 4. mysql> SELECT QUARTER(NOW()); +----------------+ | QUARTER(NOW()) | +----------------+ | 2 | +----------------+ 1 row in set (0.00 sec) mysql> SELECT QUARTER('2010-11-30'); +-----------------------+ | QUARTER('2010-11-30') | +-----------------------+ | 4 | +--------..

MySQL 날짜함수, 시간함수 PERIOD_DIFF() - 두 인자의 개월 수 차이를 구한다. PERIOD_DIFF(P1, P2) 의 형태 P1 -P2 이다. Returns the number of months between periods P1 and P2. P1 and P2 should be in the foramt YYMM or YYYYMM. Note that the period arguments P1 and P2 are not date values. mysql> SELECT PERIOD_DIFF(202001, 202001); +-----------------------------+ | PERIOD_DIFF(202001, 202001) | +-----------------------------+ | ..