topic Re: Unix time stamp in where clause in Qlik Learning Discussions

Unix time stamp in where clause

Fri, 06 Mar 2015 07:25:40 GMT

Hi there,

I am loading a sql table with unix_time stamp in it.

I have converted the unix_time by the script below

ConvertToLocalTime(timestamp((25569+(created+3600000)/(1000000000*24*3600))), 'GMT+08:00') as created

Now I want to load data which only falls between certain time stamp by defining the time frame in where clause.

But I don't know how to do it.

I wrote a script like below, but it doesn't work.

How should I define the right time in the where clause for fields which are converted unixtime?

Load <field A>,

<field B>,

ConvertToLocalTime(timestamp((25569+(<myunix_time field>+3600000)/(1000000000*24*3600))), 'GMT+08:00') as created;

SQL Select *

From <mysqltable>

Where created >'4/03/2015 21:00:00';

Re: Unix time stamp in where clause

Gysbert_Wassenaar — Fri, 06 Mar 2015 09:14:14 GMT

If your sql source contains a 'created' field then you can use a where clause with the created field. If your sql source table does not contain a 'created' field then you can't do that, but you will need to use the original unix timestamps from the 'myunix_time' field. What kind of function you need to convert the unix timestamp to a date depends on what database system you're using. SQL Server, Oracle, MySql etc all have different functions for dealing with unix timestamps.

Re: Unix time stamp in where clause

Fri, 06 Mar 2015 09:21:30 GMT

Thank you Gysbert

I am using MySQL.

The sql unix_time stamp filed name is 'created', so it is fine to put 'created' in the where clause.

Re: Unix time stamp in where clause

Gysbert_Wassenaar — Fri, 06 Mar 2015 09:27:38 GMT

Ok, then if I understand your problem correctly the unix_timestamp function could be used:

SQL SELECT * FROM ....

WHERE created > unix_timestamp('2015-03-04 21:00:00')

;

Re: Unix time stamp in where clause

Mon, 09 Mar 2015 03:30:26 GMT

I used unix_timestamp in where clause.

However, the data returned is not limited to those with 'created' after 2015-03-04 21:00:00.

I have some data with 2015-03-03 in the created field.

I think it returns all data from the table.

I also find something funny. The script returns all data from the table when I put created greater than certain timestamp in the where clause. (i.e. WHERE created > unix_timestamp('2015-03-04 21:00:00')

But no data at all return when I put an extra timestamp condition in the where clause like below.

WHERE created > unix_timestamp('2015-03-04 21:00:00')

and created <unix_timestamp('2015-03-05 21:00:00')