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
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