2

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?

4
  • it is worth to mention that previously server system time indeed have wrong timezone, but I have adjust it to my local timezone and restart mysql service and restart the machine also Jan 10, 2020 at 1:50
  • Please provide SELECT TIMEDIFF(NOW(), UTC_TIMESTAMP); and SHOW VARIABLES LIKE '%zone%';
    – Rick James
    Jan 10, 2020 at 23:24
  • @RickJames I have found out the issue. Apparently curdate() takes time from connection timezone stated by client (here in Lumen defaulted to +0) but default value would take default engine timezone. So the solution would be to explicitely change MySQL connection timezone from client Jan 22, 2020 at 4:21
  • Usually, it is possible (and practical) to set the server and MySQL up once, and ever have to futz with the TZ after that.
    – Rick James
    Jan 22, 2020 at 6:12

1 Answer 1

1

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.