I have a an existing table with timestamps and other values and I wanted to create dummy timestamps for each day in the table
For example
timestamp phase_1 phase_2 phase_3
2014-03-04 12:00:00 0 0 0
2014-03-05 02:00:00 0 0 0
2014=03-06 01:00:00 0 0 0
2014-03-07 00:00:00 0 3 1
should result to
timestamp phase_1 phase_2 phase_3
2014-03-04 00:00:00 0 0 0 --<< new
2014-03-04 12:00:00 0 0 0
2014-03-05 00:00:00 0 0 0 --<< new
2014-03-05 02:00:00 0 0 0
2014-03-06 00:00:00 0 0 0 --<< new
2014-03-06 01:00:00 0 0 0
2014-03-07 00:00:00 0 3 1
The following query works fine
`select * from Table1
right join
(select
`timestamp`
, phase_1
, phase_2
, phase_3
from Table1
union
select
date(`timestamp`)
, 0
, 1
, 2
from Table1
order by
`timestamp`) `
but I am not able to insert the result into the exisiting database
using the following query
`INSERT into Table1
select * from Table1
right join
(select
`timestamp`
, phase_1
, phase_2
, phase_3
from Table1
union
select
date(`timestamp`)
, 0
, 1
, 2
from Table1
order by
`timestamp`) `
Which i think is correct but i get a
: Every derived table must have its own alias: error which i am not sure on how to solve
Here is the fiddle enter link description here
Also, will it be possible to insert dummy timestamp and values for days which are not there at all in the table?
Suppose there are two days between the first and last rows in the table and there are no timestamps for those days in the table, will it be possible to add those two dates into the table.
I want to avoid trigger and procedures if possible as I am not at all familiar with them and since the table is dynamically generated, i think it wont be possible to use them.