Bug #72855 date_format looses track of time in stored procedure
Submitted: 3 Jun 2014 20:30 Modified: 15 Aug 2014 19:34
Reporter: R van der Wal Email Updates:
Status: No Feedback Impact on me:
None 
Category:MySQL Server: Stored Routines Severity:S3 (Non-critical)
Version:5.6.15 OS:Microsoft Windows
Assigned to: CPU Architecture:Any
Tags: date, date_format, time

[3 Jun 2014 20:30] R van der Wal
Description:
I created a stored procedure that builds a list of unix timestamps for a certain period of time. 

Why i do this doesn't matter but i want all unix timestamps from 93 days ago untill now with a 6 hour interval.

The outcome is strange, it seems that MySQL drops the minus sign somewhere (see queries) 

at a certain point the query should do this:
select date_sub(date_format(now(),"%Y-%m-%d 00:00"), interval '-1 -23' day_hour), unix_timestamp(date_sub(date_format(now(),"%Y-%m-%d 00:00"), interval '-2 -23' day_hour));

the expected outcome is "tomorrow" 23:00, but in the stored procedure i end up with "2 days ago" 23:00

How to repeat:
following stored procedure should generate unix timestamps: 

BEGIN
DECLARE v1 SMALLINT DEFAULT 93;
DECLARE myhour double DEFAULT 24;
DECLARE txtvar VARCHAR(255);

drop TEMPORARY TABLE if exists my_table;        
	 CREATE TEMPORARY TABLE my_table (myMessage varchar(255),TimeStampU DOUBLE UNSIGNED NULL) ENGINE MEMORY;
	 
while v1 >-2 do
	set v1=v1-1;
	if (v1<=0) then 
		set myhour=-23;
		while myhour <=0 do
			set txtvar=concat("'",v1," ",myhour,"'");
			insert into my_table (`TimeStampU`, myMessage) select (UNIX_TIMESTAMP(date_sub(date_format(now(),"%Y-%m-%d 00:00"), interval txtvar day_hour))*1000), txtvar;
/* REM is tore txtvar just to check if it has the right sentence */
			SET myhour = myhour +6;
		end while;
	end if;
	if (v1>0) then 
		set myhour =23;
		while myhour >=0 do
			set txtvar=concat("'",v1," ",myhour,"'");
			insert into my_table (`TimeStampU`, myMessage) select (UNIX_TIMESTAMP(date_sub(date_format(now(),"%Y-%m-%d 00:00"), interval txtvar day_hour))*1000), txtvar;
			SET myhour = myhour -6;
		end while;
	end if;	
		
end while;

select *, date_format(from_unixtime(TimeStampU/1000), "%Y-%m-%d %H:%i") from my_table;
drop TEMPORARY TABLE if exists my_table; 
END
[15 Jul 2014 19:34] Sveta Smirnova
Thank you for the report.

Please provide exact values where you see anomalies and your timezone (show variables like '%time_zone%';)
[16 Aug 2014 1:00] Bugs System
No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".