Unix time stamp in where clause

[#1]

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

[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.

[#3]

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

;

[#5]

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