Unix Datetime Filter Macro

[bryanbumgardner]

I have a SQL database (Redshift) full of Unix Epoch times in milliseconds. 

I'm trying to create a macro where the user can select the start date and end date from a calendar interface tool in a human readable format, then those inputs will modify a filter on the Unix Epoch column to select everything between 12:00:00:00 AM on the start date (The first possible millisecond), all the way up to 11:59:59:999 PM on the end date (the last possible millisecond). So the filter includes everything from the days selected. 

I've created a macro that looks like this: 

The two calendar inputs modify the start date and end date values in the filter. The code in that filter looks like this:

"unix_time_column" >= DATEDIFF(millisecond,{d '1970-01-01'},'start-date') and "tb_h" <= DATEDIFF(millisecond,{d '1970-01-01'},'end-date')

My dates are formatted as YYYY-MM-DD, and the calendar filters update them, and the DATEDIFF code changes the user input to Unix time. However, when I run this macro, it seems like I'm missing some entries. How can I build this so that it takes all the proper entries?

If I get this working right, I will publish this macro in a larger post for the community to use.

Thanks!

[SophiaF]

@bryanbumgardner - what do the action tool configurations look like?

[bryanbumgardner]

They update the "start date" and "end date" values in the formula above with a properly formatted date from the user interface. I think my problem is translating from solid dates to Unix milliseconds. I can't figure out if my filter is properly taking rows that fall on those chosen dates. 

[SophiaF · Accepted solution]

My concern would be that the Action tools are not updating properly - you most likely would be using the "Update Value (Default)" option, and you would need to select the checkbox to "Replace a Specific String" or your entire filter expression/SQL statement will be replaced. In "Replace a Specific String", you would then type in 1970-01-01, but since the dates are not distinct in the expression, both the Start Date and End Date will be replaced with one Date Interface tool:

Selecting 2017-02-02 on State Date will replace all instance of that string, giving you:

"unix_time_column" >= DATEDIFF(millisecond,{d '2017-02-02'},'start-date') and "tb_h" <= DATEDIFF(millisecond,{d '2017-02-02'},'end-date')

If that is in fact what is causing the error, you just need to make the end date distinct from the start date, and update the end date's action tool with that new date.

[bryanbumgardner]

I figured it out. My SQL was wrong!