Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

vb00494773
New Contributor III

How to convert whole time-stamp into seconds


Hi Guys ,

Could you please help me to covert whole timestamp into seconds

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
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

load

  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

];

1.png

12 Replies
MVP
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

load

  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

];

1.png

vb00494773
New Contributor III

Re: How to convert whole time-stamp into seconds

Hi Massimo Grossi,

Thanks for the reply .

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

thanks

venkatesh

MVP
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

vb00494773
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
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')

1.png

vb00494773
New Contributor III

Re: How to convert whole time-stamp into seconds

Thanks for the reply .

vb00494773
New Contributor III

Re: How to convert whole time-stamp into seconds

Thanks for the reply,

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

Please sujjest me some way.

Thanks,

Venkatesh

vb00494773
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#.

Please suggest me some way.

Thanks

Venkatesh

vb00494773
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