While writing a query in MYSQL for getting result up to Hour in dates one should use date format define below because it would not duplicate the values for the same records while matching them to dimension table.
Date_format('your_date','%d-%m-%y-H')
note: Capital H is for (1-24 hours format) while small h is for (1-12 hours format)
Example:
Select date_format(registration_date,'%d-%m-%y-%H') as "Reg_date", count(emp_id)
from emp
group by Reg_date
in order to get not NULL value one should define a separate field for selecting date from a database because defining manually first time date value in field would never bring null value and all your measures may be zero but not null...because it would generate an error i.e. can not convert nvarchar value to field INT
or another solution to it is to set default value to zero.
Example:
Select date_format(registration_date,'%d-%m-%y-%H') as "Reg_date", count(emp_id)
from emp
group by Reg_date
in order to get not NULL value one should define a separate field for selecting date from a database because defining manually first time date value in field would never bring null value and all your measures may be zero but not null...because it would generate an error i.e. can not convert nvarchar value to field INT
or another solution to it is to set default value to zero.
This comment has been removed by a blog administrator.
Thank you for your suggesion but these are the experiences which are shared so that one can find them quickly with a little bit detail.