MySQL SYSDATE()

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 fractional seconds part of that many digits.

 

SYSDATE() returns the itme at which it executes. This differs form the behavior for NOW(). which return a constant time that indicates the time at which the statement began to execute. (Within a stored function or trigger, NOW() returns the time at which the function or triggering statement began to execute.)

 

mysql> SELECT SYSDATE(), NOW();
+---------------------+---------------------+
| SYSDATE()           | NOW()               |
+---------------------+---------------------+
| 2020-04-03 17:01:25 | 2020-04-03 17:01:25 |
+---------------------+---------------------+
1 row in set (0.00 sec)

 

mysql> SELECT NOW(), SLEEP(2), NOW();
+---------------------+----------+---------------------+
| NOW()               | SLEEP(2) | NOW()               |
+---------------------+----------+---------------------+
| 2020-04-03 17:02:07 |        0 | 2020-04-03 17:02:07 |
+---------------------+----------+---------------------+
1 row in set (2.00 sec)

 

mysql> SELECT SYSDATE(), SLEEP(2), SYSDATE();
+---------------------+----------+---------------------+
| SYSDATE()           | SLEEP(2) | SYSDATE()           |
+---------------------+----------+---------------------+
| 2020-04-03 17:02:26 |        0 | 2020-04-03 17:02:28 |
+---------------------+----------+---------------------+
1 row in set (2.00 sec)

 

In addition the SET TIMESTAMP statement affects the value returned by NOW() but not by SYSDATE(). This means that timestamp settings in the binary log have no effect on invocations of SYSDATE().

 

Because SYSDATE() can return different values even within the same statement, and is not affected by SET TIMESTAMP, it is nondeterministic and therefore unsfafe for replication if statement-based binary logging is used. If theat is a problem, you can use row_based logging.

 

Alternatively, you can use the --sysdate-is-now option to cause SYSDATE() to be an alias for NOW(). This works if the option is used on boath the master and the slave.

 

The nondeterministic nature of SYSDATE() also means that indexes cannot be used for evaluating expressions that refer to it.