웹이야기

MySQL 날짜함수, 시간함수 TIME_TO_SEC() - 시간을 초로 변환해주는 함수 TIME_TO_SEC(time) 의 형태 Returns the time argument, converted to seconds. mysql> SELECT TIME_TO_SEC('23:29:33'); +-------------------------+ | TIME_TO_SEC('23:29:33') | +-------------------------+ | 84573 | +-------------------------+ 1 row in set (0.00 sec) mysql> SELECT TIME_TO_SEC(NOW()); +--------------------+ | TIME_TO_SEC(NOW()) | +-----------..

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 날짜함수, 시간함수 TIME() - 시간(time)을 추출 TIME(expr) 의 형태 Extract the time part of the time or datetime expressioin expr and returns it as a string. This function is unsafe for statement-based replication. A warning is logged if you use this function when binlog_format is set to STATEMENT. mysql> SELECT TIME(NOW()); +-------------+ | TIME(NOW()) | +-------------+ | 17:13:24 | +-------------+ 1 row ..

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 날짜함수, 시간함수 SECOND() - 시간에 대한 초를 return. SECOND(time) 의 형태 Retruns the second for time, in the range 0 to 59. mysql> SELECT SECOND(NOW()); +---------------+ | SECOND(NOW()) | +---------------+ | 13 | +---------------+ 1 row in set (0.00 sec) mysql> SELECT SECOND('10:11:07'); +--------------------+ | SECOND('10:11:07') | +--------------------+ | 7 | +--------------------+ 1 row in set (0.00 ..

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) | +-----------------------------+ | ..

MySQL 날짜함수, 시간함수 PERIOD_ADD() - 날짜에 달(월)을 더해주는 함수 PERIOD_ADD(P, N) 의 형태 Adds N months to period P(int the format YYMM or YYYYMM). Returns a value in the foramt YYYYMM. The period argument P is niot a date value. mysql> SELECT PERIOD_ADD(202001, 2); +-----------------------+ | PERIOD_ADD(202001, 2) | +-----------------------+ | 202003 | +-----------------------+ 1 row in set (0.00 sec)

MySQL 날짜함수, 시간함수 NOW() - 현재 날짜와 시간을 구하는 함수. NOW([fsp]) 의 형태 Returns the current date and the 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. The value is expressed in the session time zone. If the fsp argument is given to specify a fractional seconds precision from i to 6, the return value includes a fract..

MySQL 날짜함수, 시간함수 MONTHNAME() - 달의 이름을 구해준다. MONTHNAME(date) 의 형태 Returns the full name of the month for date. The language used for the name is controlled by the value of the lc_time_names system variables. mysql> SELECT MONTHNAME(NOW()); +------------------+ | MONTHNAME(NOW()) | +------------------+ | April | +------------------+ 1 row in set (0.00 sec) mysql> SELECT MONTHNAME('202-00-00'); +--..

MySQL 날짜함수, 시간함수 MONTH() - 주어진 날짜로부터 월을 구해준다. MONTH(date) 의 형태로 사용 Returns the month for date, in the range 1 to 12 for January to December, or 0 for dates such as '0000-00-00' or '2008-00-00' that hava a zero month part. mysql> SELECT MONTH(NOW()); +--------------+ | MONTH(NOW()) | +--------------+ | 4 | +--------------+ 1 row in set (0.00 sec) mysql> SELECT MONTH('2020-11-11'); +--------------..

MySQL 날짜함수, 시간함수 MINUTE() - 시간으로부터 분(minute)을 구하는 함수 MINUTE(time) 의 형태 Returns the minute for time, in the range of 0 to 59. mysql> SELECT MINUTE(NOW()); +---------------+ | MINUTE(NOW()) | +---------------+ | 42 | +---------------+ 1 row in set (0.00 sec) mysql> SELECT MINUTE('11:45'); +-----------------+ | MINUTE('11:45') | +-----------------+ | 45 | +-----------------+ 1 row in set (0.00 sec)