Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Unix time stamp in where clause

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

4 Replies
Gysbert_Wassenaar

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.


talk is cheap, supply exceeds demand
Not applicable
Author

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.

Gysbert_Wassenaar

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

;


talk is cheap, supply exceeds demand
Not applicable
Author

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