search

Home  >  Q&A  >  body text

Why does TIMESTAMPDIFF always return null?

I want to notify users that the server is undergoing maintenance and provide them with the remaining time until the maintenance is completed. However, I'm having trouble with the MySQL query.

This is the query I use:

SELECT TIMESTAMPDIFF(SECOND, NOW(), STR_TO_DATE(JSON_EXTRACT(VALUE_, '$.END_DATE'), '%Y-%m-%d %H:%i:%s')) AS SECONDS_LEFT FROM SETTINGS WHERE KEY_ = 'MAINTENANCE'

Unfortunately, the query always returns null values.

The following is the table structure and data for your reference.

CREATE TABLE `settings` (
  `KEY_` char(50) COLLATE utf8_unicode_ci NOT NULL,
  `VALUE_` json NOT NULL,
  UNIQUE KEY `KEY_` (`KEY_`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
KEY_ VALUE_ {"END_DATE":"2021-01-0723:46:53"}
maintain

Can you help me figure out what I might have missed?

Thank you in advance.

P粉795311321P粉795311321470 days ago713

reply all(1)I'll reply

  • P粉156983446

    P粉1569834462023-09-12 21:13:06

    solved.

    SELECT TIMESTAMPDIFF(SECOND, NOW(), STR_TO_DATE(JSON_UNQUOTE(JSON_EXTRACT(VALUE_, '$.END_DATE')), '%Y-%m-%d %H:%i:%s')) AS SECONDS_LEFT FROM SETTINGS WHERE KEY_ = 'MAINTENANCE';

    reply
    0
  • Cancelreply