Changing the time portion of timestamp

system · August 14, 2009, 5:46pm

Hi,

Definitely not an elegant formula, but it should work

=ToDate(FormatDate(RelativeDate(ToDate(FormatDate(<date variable> ,"dd-mm-yyyy") ,"dd-mm-yyyy") ,Floor((ToNumber(FormatDate(<date variable> ,"HH"))*3600 + ToNumber(Right(FormatDate(<date variable> ,"HHmm") ,2))*60 + ToNumber(FormatDate(<date variable> ,"ss")) + 3600)/86400)) ,"dd-mm-yyyy") &amp; " " &amp; FormatNumber(Floor((ToNumber(FormatDate(<date variable> ,"HH"))*3600 + ToNumber(Right(FormatDate(<date variable> ,"HHmm") ,2))*60 + ToNumber(FormatDate(<date variable> ,"ss")) + 3600)/3600) ,"00") &amp; ":" &amp; FormatNumber(Floor(Mod((ToNumber(FormatDate(<date variable> ,"HH"))*3600 + ToNumber(Right(FormatDate(<date variable> ,"HHmm") ,2))*60 + ToNumber(FormatDate(<date variable> ,"ss")) + 3600) ,3600)/60) ,"00") &amp; ":" &amp; FormatNumber(Mod(Mod((ToNumber(FormatDate(<date variable> ,"HH"))*3600 + ToNumber(Right(FormatDate(<date variable> ,"HHmm") ,2))*60 + ToNumber(FormatDate(<date variable> ,"ss")) + 3600) ,3600) ,60) ,"00") , "dd-mm-yyyy HH:mm:ss")

It should also work for dates when adding 1 hour changes the date.

Marek Chladny (BOB member since 2003-11-27)

system · August 17, 2009, 8:09am

Hi

Thanks Marek, it’ really complext formula, its working fine and giving me the result what i need. . Thanks again for your input.

Regards
Siva.M

looksmee (BOB member since 2006-02-08)

system · November 4, 2010, 4:52pm

Marek Chladny:

Hi,

Definitely not an elegant formula, but it should work

=ToDate(FormatDate(RelativeDate(ToDate(FormatDate(<date variable> ,"dd-mm-yyyy") ,"dd-mm-yyyy") ,Floor((ToNumber(FormatDate(<date variable> ,"HH"))*3600 + ToNumber(Right(FormatDate(<date variable> ,"HHmm") ,2))*60 + ToNumber(FormatDate(<date variable> ,"ss")) + 3600)/86400)) ,"dd-mm-yyyy") &amp; " " &amp; FormatNumber(Floor((ToNumber(FormatDate(<date variable> ,"HH"))*3600 + ToNumber(Right(FormatDate(<date variable> ,"HHmm") ,2))*60 + ToNumber(FormatDate(<date variable> ,"ss")) + 3600)/3600) ,"00") &amp; ":" &amp; FormatNumber(Floor(Mod((ToNumber(FormatDate(<date variable> ,"HH"))*3600 + ToNumber(Right(FormatDate(<date variable> ,"HHmm") ,2))*60 + ToNumber(FormatDate(<date variable> ,"ss")) + 3600) ,3600)/60) ,"00") &amp; ":" &amp; FormatNumber(Mod(Mod((ToNumber(FormatDate(<date variable> ,"HH"))*3600 + ToNumber(Right(FormatDate(<date variable> ,"HHmm") ,2))*60 + ToNumber(FormatDate(<date variable> ,"ss")) + 3600) ,3600) ,60) ,"00") , "dd-mm-yyyy HH:mm:ss")

It should also work for dates when adding 1 hour changes the date.

Hi Marek

i’m using Teradata DB and i have a question

i have to find the difference between 2 date…

format is HH.mm.ss

for example

1 object 15:26:48
2 object 10:00:00

i aspect: 05:26:48

can you help me??

thanks Marek

Barticchia (BOB member since 2008-06-09)

system · November 4, 2010, 8:12pm

Hi barticchia,

If this is a question how to do it in the database / universe then you should better ask the question in the Semantic Layer / Universe Designer forum and not here. Or are you doing the calculation in a DeskI/WebI report?

Marek Chladny (BOB member since 2003-11-27)

system · November 4, 2010, 9:30pm

Hi marek

i’m trying to do it into the webi report… but i don’t know if i can…
what do you think about?

Barticchia (BOB member since 2008-06-09)

system · November 4, 2010, 9:43pm

Hi,

Convert both times into number of seconds:
so for instance 15:26:48 will be 153600 + 2660 + 48 = 55608 seconds

Then get the difference of both numbers - result will be in seconds.

Then use the formula in this old topic to convert the number of seconds back to HH:MM:SS format:

Marek Chladny (BOB member since 2003-11-27)

system · November 4, 2010, 10:02pm

many thanks marek…tomorrow i’ll try
i have to use the “floor” function?

Barticchia (BOB member since 2008-06-09)

system · November 4, 2010, 10:06pm

Is there anything wrong with the function?

Marek Chladny (BOB member since 2003-11-27)

system · November 4, 2010, 10:19pm

hahahahhaha no no…just to know…

many many thanks marek

Barticchia (BOB member since 2008-06-09)

system · November 5, 2010, 6:23am

Or just
RelativeDate( [Date col]; 1/24)

Mick

mikca (BOB member since 2005-12-16)

system · November 5, 2010, 7:20am

Does the function work with fractions as well? Must be something new if it does.

Marek Chladny (BOB member since 2003-11-27)

system · November 5, 2010, 10:02am

not new always has

mikca (BOB member since 2005-12-16)

system · November 5, 2010, 10:26am

Not true in older BO versions

Marek Chladny (BOB member since 2003-11-27)

system · November 5, 2010, 11:01am

Hi marek

firs of all i wanna say thank for your help…

so i tried to do what you said…and everythings works…except in some cases…when Dep is 00:00:00…
exemple

Arr= 05:30:00
Dep=00:00:00
Result=06:30:00 (1 hour more)

i aspect the difference is 05:30:00…

i used this function (i’m italian so i tried to traslate the functions in english to italian)

this is yours :

=FormatNumber(Floor(/3600) ,“00”) & “:” &
FormatNumber(Floor(Mod( ,3600)/60) ,“00”) & “:” &
FormatNumber(Mod(Mod( ,3600) ,60) ,“00”)

this is mine :

=FormatoNumero(Arrot([_Sottrazione Secondi]/3600;0);“00”) + “:” + FormatoNumero(Arrot(Resto([_Sottrazione Secondi];3600)/60;0);“00”) + “:” + FormatoNumero(Resto(Resto([_Sottrazione Secondi];3600);60);“00”)

Formatnumber=Formatonumero
Floor=Arrot (I’m not sure about this…couse this function Rounds a number to a specified number of decimal)
Mod=Resto

Barticchia (BOB member since 2008-06-09)

system · November 5, 2010, 11:48am

Hi,

Floor() function is defined like this:

It’s not about rounding.

I am not sure what your Arrot() function does, though. :?

Marek Chladny (BOB member since 2003-11-27)

system · November 5, 2010, 12:35pm

I DID IT!!! HAHAHHAHAHHAHAHAHAHHAHA

THe function,wasn’t Arrot, but InteroInf
hahahah i know for you doesn’t mean nothing…hahah but in italian language, floor is InteroInf!!!

Really Really really thanks Marek!!!

I post here what i’ve wrote into the function (might be useful to someone)

=FormatoNumero(InteroInf([_Sottrazione Secondi]/3600);“00”) + “:” + FormatoNumero(InteroInf(Resto([_Sottrazione Secondi];3600)/60);“00”) + “:” + FormatoNumero(Resto(Resto([_Sottrazione Secondi];3600);60);“00”)

Barticchia (BOB member since 2008-06-09)

system · November 5, 2010, 2:01pm

It’s good to know that there are still people that have fun when developing reports!

Marek Chladny (BOB member since 2003-11-27)

system · November 5, 2010, 2:05pm

ahahha i really love it…even if sometimes i’d like break everything!!! hahahaha

Barticchia (BOB member since 2008-06-09)

system · July 5, 2011, 9:56am

Hi Marek,

Can you give me the generic solution to add hours to my timestamp? In one case, I need to add 2 hours and in other case 3 hours. So help me with that? Your formula works great for adding 1 hour. I just could not add 2 or 3 hours.

mradhakr (BOB member since 2011-07-05)

system · July 5, 2011, 11:03am

Welcome to B:bob:B!

Do you mean my long and complicated formula from Fri Aug 14, 2009 7:46 pm?

If yes then as you can find out, there are 4 occurrences of “+ 3600” substring in the whole formula. It represents the number of second in 1 hour (60 minutes x 60 seconds). If you change it to 7200 (which is 2 hours) or 10800 (which is 3 hours) then you will get what you need.

Marek Chladny (BOB member since 2003-11-27)