New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

New Contributor III

How to convert whole time-stamp into seconds

Hi Guys ,

example:

 11/14/2016 18:20:15.850 11/14/2016 18:20:15.860 11/14/2016 18:20:15.870 11/14/2016 18:20:15.880 11/14/2016 18:20:15.890 11/14/2016 18:20:15.900 11/14/2016 18:20:15.910 11/14/2016 18:20:15.920 11/14/2016 18:20:15.930

Thanks

Venkatesh

1 Solution

Accepted Solutions
MVP

Re: How to convert whole time-stamp into seconds

if you mean the time part of the timestamp in seconds you can use the frac function

timestamp,

date(floor(date#(timestamp, 'MM/DD/YYYY hh:mm:ss.fff')))  as date,

frac(date#(timestamp, 'MM/DD/YYYY hh:mm:ss.fff'))  as time,

frac(date#(timestamp, 'MM/DD/YYYY hh:mm:ss.fff')) * 60 * 60 * 24 as timeinsec

inline [

timestamp

11/14/2016 18:20:15.850

11/14/2016 18:20:15.860

11/14/2016 18:20:15.870

11/14/2016 18:20:15.880

11/14/2016 18:20:15.890

11/14/2016 18:20:15.900

11/14/2016 18:20:15.910

11/14/2016 18:20:15.920

11/14/2016 18:20:15.930

];

12 Replies
MVP

Re: How to convert whole time-stamp into seconds

if you mean the time part of the timestamp in seconds you can use the frac function

timestamp,

date(floor(date#(timestamp, 'MM/DD/YYYY hh:mm:ss.fff')))  as date,

frac(date#(timestamp, 'MM/DD/YYYY hh:mm:ss.fff'))  as time,

frac(date#(timestamp, 'MM/DD/YYYY hh:mm:ss.fff')) * 60 * 60 * 24 as timeinsec

inline [

timestamp

11/14/2016 18:20:15.850

11/14/2016 18:20:15.860

11/14/2016 18:20:15.870

11/14/2016 18:20:15.880

11/14/2016 18:20:15.890

11/14/2016 18:20:15.900

11/14/2016 18:20:15.910

11/14/2016 18:20:15.920

11/14/2016 18:20:15.930

];

New Contributor III

Re: How to convert whole time-stamp into seconds

Hi Massimo Grossi,

Will it convert the milliseconds also seconds . because my times stamp contains the milliseconds also

thanks

venkatesh

MVP

Re: How to convert whole time-stamp into seconds

the field timeinsec has the millisecond

my decimal separator is ","

after the "," you can see in image the millisecond

New Contributor III

Re: How to convert whole time-stamp into seconds

Thanks perfectly fine , i got it .

How do we convert the time in sec field  into time stamp again.

Basically i want to check the conversation is correct or not .

Thanks

Venkatesh

MVP

Re: How to convert whole time-stamp into seconds

you can sum the date and time part

last column is

Timestamp(timeinsec / (60*60*24) + date, 'MM/DD/YYYY, hh:mm:ss.fff')

New Contributor III

Re: How to convert whole time-stamp into seconds

New Contributor III

Re: How to convert whole time-stamp into seconds

The above is not working in qliksense exspecially when i use Frac fucntion after date#.

Thanks,

Venkatesh

New Contributor III

Re: How to convert whole time-stamp into seconds

Thanks for the reply on the case.

The code which you have provided is working in qlikview but not in qlik sense .

Especially when I add frac function after Date#.

Thanks

Venkatesh

New Contributor III

Re: How to convert whole time-stamp into seconds

Hi ,

My main time stamp in the below format.

2016-11-14 18:04:19.06