Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello,
I am extracting only the Time from a DateTime collumn through this code line:
to_char(l.dat_transacao, 'hh24:mi:ss' ) as "Horário"
My table is like this:
Horário | Valor |
16:36:16 | 5 |
16:36:21 | 3 |
16:36:23 | 6 |
But I want all the seconds to be shown. How can I get the table to be like this?
Horário | Valor |
16:36:16 | 5 |
16:36:17 | null |
16:36:18 | null |
16:36:19 | null |
16:36:20 | null |
16:36:21 | 3 |
16:36:22 | null |
16:36:23 | 6 |
EDIT: This table is used to generate a line chart. If I can add a custom dimension with all the seconds it's already a solution.
hello
why don't you create a table with all time values first
and then join your sql request on key time ?
may be Try a custom expression and drag it to the front.. You need a bit of an hack to get this work
if you define this as an expression , you can change the format into time using the numebr tab
ValueLoop(Time#('16:36:16','hh:mm:ss'),Time#('16:36:23','hh:mm:ss'),MakeTime(0,0,1))
if not, pl post a sample app
Try this,
Data:
load Sales,
Timestamp#(date_time,'MM/DD/YY hh:mm:ss') as date_time;
Load * inline [
date_time,Sales
03/12/18 16:36:16, 20
03/12/18 16:36:23, 40
03/12/18 16:36:21, 10
];
// Master Caendar
Let vMinDate = floor(Timestamp#('03/12/18 16:36:16','MM/DD/YY hh:mm:ss'));
Let vMaxDate = floor(Timestamp#('03/12/18 16:36:23','MM/DD/YY hh:mm:ss'));
CalendarTemp:
LOAD
TimeStamp($(vMinDate) + (RecNo()/60/60/24) + (IterNo() -1),'MM/DD/YY hh:mm:ss') AS date_time,
TimeStamp($(vMinDate) + (RecNo()/60/60/24) + (IterNo() -1),'hh:mm:ss') AS time
AUTOGENERATE 86399 WHILE Num($(vMinDate) + IterNo() -1) <= Num($(vMaxDate));
At front end you can add calculated dimension as
=if(date_time <= '03/12/18 16:36:23' and date_time >= '03/12/18 16:36:16',time)