MySQL CURDATE() and CURRENT_TIMESTAMP return different value

dateMySQLmysql-8.0timezone

I am facing a little confusion because of CURRENT_DATE value and CURRENT_TIMESTAMP value shows different date on MySQL.

Scenario :
1) User calls API from Lumen backend to insert data on a table. One column used CURRENT_DATE (sales_date) as insert value and one column use CURRENT_TIMESTAMP (temp_timestamp) as default value

2) Query exactly looks like this :

$_reg = DB::insert("INSERT INTO t_sales(sales_no,ref_no, sales_date,customer_id,branch_id,user_id,sales_info) VALUES(?,?,curdate(),?,?,?,?)", [$a, $b, $cid, $branch, $uid,$info]);

3) Querying the result would give different value. Date column shows '2020-01-09' AND TIMESTAMP column shows '2020-01-10 06:44:33' (as per date insert.. I am on GMT+0700 timezone)

4) weirder results, if I log into mysql using CLI or SQLYog from my PC and issue SELECT CURRENT_DATE, CURRENT_TIMESTAMP both shows correct date '2020-01-10'

Environment :

  1. Lumen backend located on same physical machine as MySQL backend
  2. Lumen explicitely set timezone to Asia/Jakarta (which shouldn't matter as it is in PHP)
  3. MySQL version is 8.0 with time_zone set to SYSTEM on my.cnf
  4. System use Ubuntu 18.04 server with tzdata set to Asia/Jakarta (UTC+7). timedatectl shown like this as per now (of course when I insert earlier it shows 2020-01-09 for Universal Time and RTC time)

Local time: Fri 2020-01-10 06:33:37 WIB
Universal time: Fri 2020-01-10 01:33:37 UTC
RTC time: Fri 2020-01-10 01:33:38
Time zone: Asia/Jakarta (WIB, +0700)
System clock synchronized: yes
systemd-timesyncd.service active: yes
RTC in local TZ: no

Why CURRENT_DATE and CURRENT_TIMESTAMP have different value?

Best Answer

Why CURRENT_DATE and CURRENT_TIMESTAMP have different value?

They returns values which have different datatypes.

MySQL 8.0 Reference Manual / Functions and Operators / Date and Time Functions

CURRENT_DATE and CURRENT_DATE() are synonyms for CURDATE().

CURDATE()

Returns the current date as a value in 'YYYY-MM-DD' or YYYYMMDD format, depending on whether the function is used in string or numeric context.

CURRENT_TIMESTAMP and CURRENT_TIMESTAMP() are synonyms for NOW().

NOW([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. The value is expressed in the session time zone.

If the fsp argument is given to specify a fractional seconds precision from 0 to 6, the return value includes a fractional seconds part of that many digits.

NOW() returns 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.) This differs from the behavior for SYSDATE(), which returns the exact time at which it executes.

Related Question