Yesterday's date (Unix) to variable

[RIchardC]

I needed to create a variable with yesterday’s date in Unix format.

This flow works, but it seems like the hard way.

If anyone can use this, I’m glad. If anyone knows a better way, I’d be happy to see it.

Yesterday's date (Unix) to variable – richardc

[bruno29a]

Hi @RIchardC , I think your logic is good in general, and as we always say, there are more than 1 way to do things in Knime.

FYI, the first 3 nodes that you have can simply be replaced by the Create Date&Time Range node:

Create Date&Time Range

This would be my version of the workflow:

As I mentioned, my first node would actually do the same thing that your first 3 nodes are doing. Given that the requirement is to get yesterday’s date (in this case I would say yesterday’s timestamp, meaning current timestamp - 1 day), I use the Date&Time Shift to do this.

Note, if you want yesterday’s date, you can modify the Create Date&Time Range node and choose date instead of date&time.

After that, I convert to UNIX timestamp, and convert to variable.

Based on what you did, you calculated the dates manually (1 day ago and 1 week ago). If you need to have different time stamps like you did (1 day ago and 1 week ago), I would go with what you did, which basically allows you to calculate different timestamps in just 1 node.

Also, it’s a matter of either calculating the dates that you want, and then convert to UNIX timestamp, which is what I did, or a matter of converting to UNIX timestamp and then calculate the dates, which is what you did, which ever you prefer in the end. It’s just that I made use of Knime’s date nodes rather than manually calculating the seconds

Here’s my workflow: Yesterday’s UNIX timestamp to variable.knwf (14.1 KB)

[takbb]

Hi @RichardC,

I did wonder whether (if you are specifically wanting the Unix timestamp for yesterday), you could lower the node count slightly by calculating the difference between Jan 2nd 1970 and today’s date, which ought to give the correct result

However, there are two aspects to this. Firstly if it were just fitted into your flow as it stood, it would return a negative (which I know you handle already in the Column Expression node that I was seeking to avoid, so some other rework would be required. Further, it would require additional adjustment if you wanted “a week ago”, “a month ago” so isn’t quite as good generically, and doesn’t really improve your solution!

So what you (and @bruno29a ) have with regard to calculating with standard (non-code) solutions is I think about as good as it gets for determining a timestamp for a given date.

However… I would question the purpose to which you want to put your “UNIX timestamp” because what this solution doesn’t take into account is that the true UNIX timestamp is based on UTC so if you are wanting this for some type of comparison with an actual unix timestamp, this solution won’t necessarily be correct for your use case.

If you want an accurate Unix Timestamp based on UTC, so it is the same no matter where in the world you are, then you need to take into account your time zone, which would add further complication.

The “simple” alternative to that, if you are happy for a couple of lines of code
(I put it on two lines, for improved clarity, but it can be done on one line), is to use Java. The Java Edit Variable can easily do it, as the java System.currentTimeMillis() method returns milliseconds since the same Epoch as Unix. To return the current Unix timestamp in seconds requires just these lines of code:

Long sysSeconds=(System.currentTimeMillis() / 1000) ;
out_CurrentUnixTimeStamp=sysSeconds.intValue();

To test it, you can compare this result with a website such as https://currentmillis.com/

Of course you can either have the Java Edit variable return this value for “now” which you can manipulate in other nodes with the multiples of 86400 seconds per day that you have done, or you could amend the code in the Java node to do the entire required calculation. Your choice.

Note: Although java holds the milliseconds time in a Long, the Java Edit variable is (frustratingly imho) incapable of returning a Long and so has to convert to an Int which has a far smaller number range. However, it can cope for about the next 6000 days (16 years), by which point hopefully the Java Edit Variable, and a number of other nodes will have been modified to handle longer integers! Either that or you have to have it return a Double and then turn it into a column, then convert with a Double to Int node (set to return a Long), and then turn it back into a flow variable which I feel is just a little painful for what should be such a trivial exercise. Alternatively, maybe you could have java return the number as a String and then convert it to a Long with String to Number, but you still need to do conversion to a column and back again for that to work so it probably doesn’t gain anything. Personally, I think I would be happy to wait the 16 years

I hope that helps more than it confuses!