topic Re: How to convert Unix Time Stamp in QlikView

How to convert Unix Time Stamp

Wed, 09 Sep 2015 10:43:18 GMT

Hi,

I have a database with one of the fields "Receiveddata" in format like:

1014937200

1025521200

etc...

In Epoch Converter we have...

1025521200

MT: Thu, 28 Feb 2002 23:00:00 GMT
Your time zone: ‎01‎.‎03‎.‎2002‎ ‎1‎:‎00‎:‎00 GMT+2:00

help me please, to convert to the Human date...

"ODBC CONNECT TO ...............................

LOAD RECEIVEDDATE,
TYPE,
Date('RECEIVEDDATE','MM/DD/YYYY') as RECEIVEDDATE1; (is not working...)

SQL SELECT RECEIVEDDATE,
TYPE,

............"

is not working...

Re: How to convert Unix Time Stamp

PrashantSangle — Wed, 09 Sep 2015 11:37:33 GMT

Hi,

Try,

Date(RECEIVEDDATE,'MM/DD/YYYY') as RECEIVEDDATE1

Regards

Re: How to convert Unix Time Stamp

PrashantSangle — Wed, 09 Sep 2015 11:38:35 GMT

Hi,

also refer below thread

Julian Date conversion in the script

Regards

Re: How to convert Unix Time Stamp

Wed, 09 Sep 2015 11:51:00 GMT

Hi,

See the link below it might help u,

Convert from a Unix timestamp to the local time in QlikView · GitHub.

Re: How to convert Unix Time Stamp

jonathandienst — Wed, 09 Sep 2015 11:53:47 GMT

If this is based on the Unix epoch, then perhaps this:

=Date(Date#('19700101', 'YYYYMMDD') + (Receiveddata / 86400))

=TimeStamp(Date#('19700101', 'YYYYMMDD') + (Receiveddata / 86400))

Re: How to convert Unix Time Stamp

avinashelite — Wed, 09 Sep 2015 11:54:10 GMT

try like this:

timestamp((timecompleted/ 86400 + 25569),'DD MMM YYYY h:mm:ss[.fff] TT'))

Re: How to convert Unix Time Stamp

Wed, 09 Sep 2015 12:06:00 GMT

SQL##f - SqlState: S1000, ErrorCode: 936, ErrorMsg: [Oracle][ODBC][Ora]ORA-00936: missing expression

SQL SELECT RECEIVEDDATE,
TYPE,

.....

Re: How to convert Unix Time Stamp

jonathandienst — Wed, 09 Sep 2015 12:12:22 GMT

The expression must be in the QV part of the LOAD, not the SQL part:

LOAD RECEIVEDDATE As UNIXDATE,

TYPE,

Date(Floor(Date#('19700101', 'YYYYMMDD') + (RECEIVEDDATE / 86400)) as RECEIVEDDATE;

...

SQL SELECT RECEIVEDDATE,

TYPE,

...

Re: How to convert Unix Time Stamp

Thu, 10 Sep 2015 06:48:55 GMT

Thank You-It's fine

Re: How to convert Unix Time Stamp

Kushal_Chawda — Thu, 10 Sep 2015 07:03:26 GMT

try this,

=timestamp((YourDateField/(24*60*60)) + (2 + date#('1/1/1970') - date#('1/1/1900')),'DD/MM/YYYY hh:mm:ss')

Re: How to convert Unix Time Stamp

engishfaque — Thu, 10 Sep 2015 07:40:33 GMT

Dear Viorel,

Please check,

Unix timestamp from a string to date

DB2 time converter

Kind regards,

Ishfaque Ahmed

Re: How to convert Unix Time Stamp

MarcoWedel — Thu, 10 Sep 2015 08:21:24 GMT

Date settings

UNIX DATE to Qlikview DATE

Maybe helpful

Regards

Marco

Re: How to convert Unix Time Stamp

diwaskarki — Mon, 22 Jan 2018 19:30:48 GMT

date(date(floor(RECEIVEDDATE/1000/60/60/24)) +date('1970-01-01')) As ReceivedDate,