I'm trying to calculate the time remaining based on a target date. I have a target date of July 4, 2022 10:00:00 and I want to subtract that from today's date.
I first did SET @var_name = "July 4, 2022 10:00:00", followed by SELECT datediff(now(),@var_name) but it keeps returning a null value. Is there a way where I can have it display the number of days, hours and minutes remaining from the target date?
You can follow these steps:
format your string into the DATE format with STR_TO_DATE:
STR_TO_DATE(@var_name, '%M %d, %Y %h:%i:%s')
get difference in seconds between dates with TIMESTAMPDIFF:
TIMESTAMPDIFF(SECOND, NOW(), STR_TO_DATE(...))
transform seconds into time with SEC_TO_TIME:
SEC_TO_TIME(TIMESTAMPDIFF(...))
extract hours, minutes and seconds from time using DATE_FORMAT:
DATE_FORMAT(SEC_TO_TIME(...), '%H hours %i minutes %s seconds')
extract days separately as an integer division between hours and 24:
HOUR(SEC_TO_TIME(...)) DIV 24
Check the full query followingly:
SET @var_name = "July 4, 2022 10:00:00";
WITH cte AS (
SELECT SEC_TO_TIME(
TIMESTAMPDIFF(SECOND,
NOW(),
STR_TO_DATE(@var_name,
'%M %d, %Y %h:%i:%s'))) AS seconds )
SELECT CONCAT(HOUR(seconds) DIV 24,
' days ',
DATE_FORMAT(seconds,
'%H hours %i minutes %s seconds'))
FROM cte
Try it here.
SELECT datediff(now(),'2022-06-04 10:00:00')