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'
- Lumen backend located on same physical machine as MySQL backend
- Lumen explicitely set timezone to Asia/Jakarta (which shouldn't matter as it is in PHP)
- MySQL version is 8.0 with time_zone set to SYSTEM on my.cnf
- 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?