How to Calculate N days ago from Today

All Dates in DronaHQ platform are stored in Unix Time

Considering Time

To Calculate N days ago from Today, we can use this formula and bind it to DateTime control -

STRTOUNIX(TODAY()) - N * 24 * 60 * 60 * 1000

Where, N is number of days before today.

Above formula will consider Current time as well in the result.

Formula Explanation

  • TODAY() function will return Date object with current date & time values.
  • STRTOUNIX(TODAY()) will convert given date object to corresponding UnixTime value which is number type
  • Now, we will subtract number of days in milliseconds using N * 24 * 60 * 60 * 1000 formula. Value of 1 day in milliseconds is 24 * 60 * 60 * 1000.

Now the resultant UNIX Time will be required UnixTime which is N days ago time from current date & time

Example

For N=2, you can see the result of DateTime Control, one having formula TODAY() and second having formula STRTOUNIX(TODAY()) - 2 * 24 * 60 * 60 * 1000

Independant of Time

To Calculate N days ago from Today, we can use this formula and bind it to Date control -

STRTOUNIX(DATESTR(TODAY(),“DD-MM-YYYY”),“DD-MM-YYYY”)- N * 24 * 60 * 60 * 1000

Where, N is number of days before today.

Above formula will not consider Current time. By Default Date control internally automatically discards time of the current date but in case you want to use this in any other control than above formula is advisable.

Formula Explanation

  • TODAY() function will return Date object with current date & time values.
  • DATESTR(TODAY(),“DD-MM-YYYY”) will return the given date as String Text as DD-MM-YYYY format
  • STRTOUNIX(DATESTR(TODAY(),“DD-MM-YYYY”),“DD-MM-YYYY”) will convert given date String to corresponding UnixTime value which is number type
  • Now, we will subtract number of days in milliseconds using N * 24 * 60 * 60 * 1000 formula. Value of 1 day in milliseconds is 24 * 60 * 60 * 1000.

Now the resultant UNIX Time will be required UnixTime which is N days ago time from current date and time will be 00:00 in UTC timezone

Example

For N=2, you can see the result of DateTime & Date Control, where first one having formula TODAY() and second & Third one having formula STRTOUNIX(DATESTR(TODAY(),“DD-MM-YYYY”),“DD-MM-YYYY”)- 2 * 24 * 60 * 60 * 1000

Here, you can notice, the 3rd Date Time control have its time set to 12:00AM indicating no time of that day is being considered. Depending on your timezone, this may vary as DateTime control by default display datetime in local timezone.

For More Info on DATESTR function refer here
For More Info on STRTOUNIX function refer here

Note - For Calculating - N days after from Today instead of Subtracting days you can add days