Round off Time in Timestamp to Hours

[haneeshmarella]

Hi,

I have the following timestamp which contains 'Date Field' and 'Time String' that gives a result of the below picture.

TimeStamp((TimeStamp#(DATE_CREATED & IF( LEN(TIME_CREATED) = 3 , ('000' & TIME_CREATED), IF  ( LEN(TIME_CREATED) =4  , '00' & TIME_CREATED,IF( LEN(TIME_CREATED) =5  , '0' & TIME_CREATED, TIME_CREATED ))),'YYYYMMDDhhmmss' )),'MM/DD/YYYY hh:mm:ss') as DATE_TIME_EST

I want to have a result such that it concatenates the hours together. Ex. 06/14/2018 00:01:08 and 06/14/2018 00:02:36 show as 06/14/2018 12 AM or 06/14/2018 00 and sums up their respective results.

Also, how do I subtract an hour from the above result to get my time in CST as the result is in EST?

[MarcoWedel]

I guess as you already have DATE_CREATED there is no need for rounding your timestamps of fto daystart.

Timestamp(Date#(DATE_CREATED,'MM/DD/YYYY'),'MM/DD/YYYY hh TT')

should do the trick.

hope this helps

regards

Marco

[haneeshmarella]

Hi Marco,

Your answer is not applicable to my scenario.

The date is of string format YYYYMMDD. The above timestamp function I have utilized gives me a timestamp output based on the string format input of date and time.

I need to get the hours rounded off for the timestamp output shown in the screenshot above. I also gave an example below the screenshot of what I am looking for. Hope this clears my question.

[MarcoWedel]

sorry, wrong format.

Try

Timestamp(Date#(DATE_CREATED,'YYYYMMDD'),'MM/DD/YYYY hh TT') 

instead.

Marco

[haneeshmarella]

There is no time in DATE_CREATED.

The time comes from IF( LEN(TIME_CREATED) = 3 , ('000' & TIME_CREATED), IF  ( LEN(TIME_CREATED) =4  , '00' & TIME_CREATED,IF( LEN(TIME_CREATED) =5  , '0' & TIME_CREATED, TIME_CREATED and is combined with the date string that comes from DATE_CREATED to form the time stamp format of MM/DD/YYYY hh:mm:ss. I need to convert this output to MM/DD/YYYY hh where the hours are concatenated by summing their respective rows. Makes sense?

[MarcoWedel]

can you elaborate on the "concatenating hours by summing their rows" part?

[haneeshmarella]

Sure, if there are two rows of timestamps:

Timestamp                                        Count of Orders

06/15/2018 14:30:32                                   20

06/15/2018 14:45:02                                   10

                                                          Timestamp               Count of Orders

So I want it to be in the format of    06/15/2018 14                     30

I want to convert the above timestamp formula (in my original question) I implemented that results in MM/DD/YYYY hh:mm:ss into the format of MM/DD/YYYY hh. Hope this clarifies.

[Colin-Albert]

Haneesh - You need to round yout timestamp to remove the minute values, then your count will be correct.

The issue is the same as my answer to your other post  String Conversion to Date, Time, Timestamp in certain format