How to Show value in a Proper Timestamp Format

[brijeshvma]

Hi all , I am fetching a data from big Query . everything in fine . but Timestamp format i am getting in not Perfect .

Kindly help me to achieve this. please find the attachment.

i Attached , Qlikview output image , Bigquery image , sample data in excel and qvw .

[settu_periasamy]

May be try this..

SELECT TIMESTAMP(TimestampField);

in QV script,

ConverttoLocalTime(Date#('TimestampField','YYYY-MM-DD hh:mm:ss')) as Local_Timestamp

-This will convert your UTC time to Local system time format

[brijeshvma]

Hi Settu,

I tried your given expression , nothing happend . can you check with my csv file or qvd that i attached.

[swuehl · Accepted solution]

I think your date time field is given in epoch time / unix time after your INTEGER conversion.

Epoch Converter - Unix Timestamp Converter

Either correct the SQL statement to bring in the textual representation, or transform your integer to Qlik date time:

Re: How to convert Unix Time Stamp

[settu_periasamy]

Based on Swehul suggestion, Try this script.

LOAD   SynchronousQuery_date,
     TimeStamp(Makedate(1970,1,1)+ SynchronousQuery_date_time_IST /24/60/60) as SynchronousQuery_date_time_IST,
      TimeStamp(Makedate(1970,1,1)+ SynchronousQuery_datetime /24/60/60/1000) as SynchronousQuery_datetime,
      SynchronousQuery_device
FROM DateCheck.qvd (qvd);

Link :  UNIX DATE to Qlikview DATE‌

Edit: Missed one Field, now its added, Sample Attached

[brijeshvma]

Hi ,

Thanks swuehl and settu_periasamy,

Based on the swuehl suggestion . i got this community.qlik.com/message/700732#700732 . that works ..

same link as settu as well posted.

[brijeshvma]

Thanks for your time settu , swuehl and everyone