Exactly How Qlik Sense Convert Epoch Time?

[wuming79]

I'm not sure why when I search for examples, I always get from Qlik view but not much from Qlik Sense.

I do I actually convert a column whose field name is epochtime to a format that is YYYY-MM-DD?

I tried the following:

[time]:

SELECT

  date(floor(timestart/ 86400 + 25569)) as DateStart,

  date(floor(timeend/ 86400 + 25569)) as DateEnd

FROM testDB.time;

But when I put DateStart and DateEnd in a table, it only shows me the headers without any date in my column.

DateStart	DateEnd
Empty	Empty
Empty	Empty

[Gysbert_Wassenaar]

I'm not sure why when I search for examples, I always get from Qlik view but not much from Qlik Sense.

1. Because Qlikview exists since 1993 and Qlik Sense for only a fraction of that time.

2. Because people post in the wrong places

3. Because it doesn't matter since the functions and syntax you use in expressions are exactly the same.

If you get empty values for DateStart and DateEnd you better check what kind of values actually exist in the fields timestart and timeend. If those are not numeric values but text values then the floor function will return null. Perhaps you need to use the Date# function on those values first to convert strings into dates.

[wuming79]

Hi Gysberf,

Oh, actually how can I check if the value is in string or number?

I tried the code below with #

[time]:

SELECT

  date#(floor(timestart/ 86400 + 25569)) as DateStart,

  date#(floor(timeend/ 86400 + 25569)) as DateEnd

FROM testDB.time;

Qliksense returned error:

The following error occurred:

QVX_UNEXPECTED_END_OF_DATA: ERROR [42S22] [Qlik][MySQL] Unknown column 'date' in 'field list'

[wuming79]

Just tried the following:

LOAD timestart,

  Num#(timestart) as newstimeStart,

    timeend,

  Num#(timeend) as newstimeEnd;

[time]:

SELECT

  Date(floor(newstimeStart/ 86400 + 25569)) as DateStart,

  Date(floor(newstimeEnd/ 86400 + 25569)) as DateEnd

FROM testDB.time;

But this returned another error:

The following error occurred:

Field 'timestart' not found

---

The error occurred here:

?

Not sure why does Qlik Sense still look for 'timestart' when I LOAD it before the table?

[Gysbert_Wassenaar]

Actually that's MySQL that's saying it doesn't understand the date# function. Which makes sense since it's a Qlik Sense function, not an MySQL function.

[Gysbert_Wassenaar]

You're using a preceding load in which records are processed bottom to top. So first the SELECT gets the data from the database and then pass them up to the LOAD that's preceding it. Since the SELECT statement doesn't return a timestart field you get the error.

Try to find out what's actually in the newstimeStart and newstimeEnd fields by only loading those fields without any further processing.

SELECT newstimeStart, newstimeEnd FROM testDB.time

[wuming79]

Do you mean to

SELECT  timestart, timeend FROM testDB.time?

timsstart and timeend is in these format but I can't tell if it is string or number.

timestart	timeend
1529251200	1529254800
1529251400	1529252000
1529251600	1529255600
1529251800	1529259200

SELECT newstimeStart, newstimeEnd FROM testDB.time will gives me error as

The following error occurred:

QVX_UNEXPECTED_END_OF_DATA: ERROR [42S22] [Qlik][MySQL] Unknown column 'newstimeStart' in 'field list'

[wuming79]

So thinking that it process the SELECT before load, I thought I could convert the timestart to num first without LOAD.

SELECT Num#(timestart) as newstimeStart From testDB.time;

I get the error below:

The following error occurred:

QVX_UNEXPECTED_END_OF_DATA: ERROR [42S22] [Qlik][MySQL] Unknown column 'Num' in 'field list'

I tried:

SELECT Num#(timestart) as newstimeStart From testDB.time;

and I get the error:

FUNCTION testDB.Num does not exist

I hope this is not a silly question....really having a hard time understanding how Qlik View answers can be used in Qlik Sense...

[Gysbert_Wassenaar · Accepted solution]

To start with you should stop using Qlik functions in SQL SELECT statements. The only thing that Qlik Sense does with an SQL statement is send it to the database (actually to the odbc or oledb driver). Your database does not understand Qlik functions so you should use them in SQL statements.

Next use only field names that actually exist in your database. If you post Date(floor(newstimeStart/ 86400 + 25569)) as DateStart, then I assume there is a field in your database with the name newstimeStart.

So load data from the database:

[testdb.time]:

SELECT timestart, timeend FROM testDB.time;

Then take a look at the data. Is any data loaded? Do the values look like numbers? What is the result if you add a kpi object to the app with the measure sum(timestart)? It should be a very large number. Is it? If it is try the next step and add another table that loads the data from testdb.time:

[testdb.time2]:

LOAD

     date(floor(timestart/86400)+25569,'YYYY-MM-DD') as DateStart,

     date(floor(timeend/86400)+25569,'YYYY-MM-DD') as DateEnd

RESIDENT

     [testdb.time]

     ;

[wuming79]

I tried to combine and it works!  Thanks Gysbert!!

[time]:

LOAD

     date(floor(timestart/86400)+25569,'YYYY-MM-DD') as DateStart,

     date(floor(timeend/86400)+25569,'YYYY-MM-DD') as DateEnd;

SELECT

     timestart, timeend

FROM testDB.time;