웹이야기

MySQL 날짜함수, 시간함수 UTC_TIME() - UTC_TIME, UTC_TIME([fsp]) 의 형태 Returns the current UTC time as a value 'hh:mm:ss' or hhmmss format, depending on whether the function is used in string or numeric context. If the fsp argument is given to sepcify a fractional seconds precision form 0 to 6, the return value includes a fractional seconds part of that many digits. mysql> SELECT UTC_TIME(), UTC_TIME+0 ; ..

MySQL 날짜함수, 시간함수 UTC_DATE() - UTC_DATE, UTC_DATE() 의 형태 Returns the current UTC date as a value in 'YYYY-MM-DD' or YYYYMMDD format, depending on whether the function is used in string or numeric context. SELECTmysql> SELECT UTC_DATE(), UTC_DATE()+0 ; +------------+--------------+ | UTC_DATE() | UTC_DATE()+0 | +------------+--------------+ | 2020-04-05 | 20200405 | +------------+--------------+ 1..

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