KM dates & times: Notes on anchors, intervals, and translations

Finding that I wasn’t very familiar with KM date calculations and date formatting, I sketched some draft notes and examples.

(For initial simplicity, I have kept to the Unix Epoch Seconds functions, and left the Julian Day functions aside, though they are clearly indispensable for historians, and convenient for quick counts of days between two dates )

Here are some rough draft notes (now incorporating several edits and fixes suggested by Peter) on:

  • Anchor date-times ( absolute calendar dates, now, and system events like start-of-session or macro launch)
  • Offsets (relative date-times constructed as anchor + Interval, or the time elapsed between two dates), and
  • Translations (rewriting Unix seconds as formatted date-times, converting between Unix and Julian, etc)

Working with dates and times in KM

Date-time Anchors

Now

  • NOW()

      // () → intEpochSeconds
          %Calculate%NOW()% → 1440348448
    
  • (or) TIME()

      // () → intGMTEpochSeconds
          %Calculate%TIME% → 1440405739
    

Calendar

  • TIME(intYear, intMonth, intDay)

       // intYear → intMonth → intDay → intGMTEpochSeconds
           %Calculate%TIME(2015, 8, 23)% → 1440288000
    
  • TIME(intYear, intMonth, intDay, intHours, intMins, intSeconds)

       // intYear → intMonth → intDay → 
       //     intHours → intMins → intSeconds → intGMTEpochSeconds
           %Calculate%TIME(2015, 8, 23, 18, 44, 0)% → 1440355440
    

System events

  • system start

    • SECONDS()

         // () → floatSeconds
            %Calculate%SECONDS()% → 96789.996928
      
    • MILLISECONDS()

         // () → floatMilliSeconds
            %Calculate%MILLISECONDS()% → 97488480.361
      
    • MICROSECONDS()

         // () → intMicroSeconds
            %Calculate%MICROSECONDS()% → 97500344233
      
  • macro triggered

    • TRIGGERTIME()

         // () → floatEpochSeconds
            %Calculate%TRIGGERTIME()% → 1440349785.37247396
      
  • last user input

    • IDLE()

        // () → floatSeconds
           %Calculate%IDLE()% → 0.28027293
      

Date-time Offsets

(Date-time + interval) → new date-time

  • Relative to NOW()

    • Recognised interval strings

        Seconds
        Minutes
        Hours
        Days
        Weeks
        Months
        Years
      
    • Examples

      • tomorrow
        %ICUDateTimePlus%1%Days%EEE, MMM d, yyyy%

      • this coming thursday
        %ICUDateTimePlus%(7+5-DOW(NOW)) MOD 7%Days%EEE, MMM d, yyyy%
        Note: DOW(intEpochSeconds) → intDayOfWeek [1=Sunday]

      • beginning of next month
        %ICUDateTimeFor%TIME(YEAR(), MONTH()+1, 1)%EEE, MMM d, yyyy%

  • Relative to some other anchor date

    • two weeks before Sep 28 2015
      %ICUDateTimeFor%TIME(2015, 9, 28-14)%EEE, MMM d, yyyy%

    • Week 20 of 2016
      %ICUDateTimeFor%TIME(2016, 1, 1+19*7)%EEE, MMM d, yyyy%

    • Six weeks after Apr 20 2016
      %ICUDateTimeFor%TIME(2016, 4, 20+6*7)%EEE, MMM d, yyyy%

Difference between two date-times

  • hours left till midnight
    %Calculate%(TIME(YEAR(), MONTH(), DAY()+1, 0, 0, 0) - NOW() - GMTOFFSET())/3600%

  • days between Jan 10 and March 15 2016
    %Calculate%(TIME(2016, 3, 15) - TIME(2016, 1, 10))/(24 * 3600)%

  • remaining weeks this year
    %Calculate%FLOOR((TIME(YEAR(), 12, 31) - NOW()) / (7 * 24 * 3600))%

Translations

date → string

    //  intUnixEpochSeconds → string
        %ICUDateTimeFor% 1483142400 %EEE, MMM d, yyyy%

    // Note, the epoch seconds returned by TIME() are GMT,
    // so pinning a day down by midday (rather than midnight) may be prudent
    // ( allowing a margin for variations in time zones )
    //  intYear → intMonth → intDay → intHour, intMins, intSeconds, string
        %ICUDateTimeFor% TIME(2015, 8, 23,12,0,0) %EEE, MMM d, yyyy%

GMT → local time

    //  () → intDifferenceInSeconds
        %Calculate%GMTOFFSET()%   → 3600

Unix epoch seconds ⇄ Julian days

    // intUnixEpochSeconds → floatJulianDays
       %Calculate%TIME2JD( 1483142400 )% → 2457753.5

    // floatJulianDays → intUnixEpochSeconds
       %Calculate%JD2TIME( 2457753.5 )% → 1483142400
5 Likes

and string → date ?

( I didn’t notice any functions for parsing formal or informal date strings to Epoch or Julian, but of course there’s a whole world of localisation issues there … )

This great Rob! Very helpful! :+1:
EverNoted!

@peternlewis: Can we put this in the KM wiki?

When I search the wiki on “date calculation(s)” it returns nothing.
Rob’s title is good, but the wiki page would also need keywords of:

date, dates, date math, date calculation, date calculations, functions, formulas

so it would be easy to find for people who don’t know the KM vernacular.

If the KM wiki doesn’t support synonyms, maybe we need to add a footer to each page with keywords for that page.

I have made a wiki page, but it requires some cleaning up as the forum and the wiki do not entirely agree on formatting codes (I would dearly love to have a Discourse wiki equivalent, but I don’t know how long until that might happen, if ever).

Also, there may be some more edits to the post before its settled down. Here are some comments:

This will not give a future time if the DOW is 6 or 7 (ie, Friday or Saturday) - you’ll get the past Thursday instead of the future Thursday. To ensure it is positive, you need to add 7 and MOD 7. Like this:

 %ICUDateTimePlus%(7+5-DOW(NOW)) MOD 7%Days%EEE, MMM d, yyyy%

Also, these:

two weeks before Sep 28 2015
    %ICUDateTimeFor%TIME(2015, 9, 28)-(14*24*3600)%EEE, MMM d, yyyy%
Week 20 of 2016
    %ICUDateTimeFor%TIME(2016, 1, 1)+(19*7*24*3600)%EEE, MMM d, yyyy%
Six weeks after Apr 20 2016
    %ICUDateTimeFor%TIME(2016, 4, 20)+(6*7*24*3600)%EEE, MMM d, yyyy%

are more simply done by adding/subtracting in the date column.

%ICUDateTimeFor%TIME(2015, 9, 28-14)%EEE, MMM d, yyyy%
%ICUDateTimeFor%TIME(2016, 1, 1+19*7)%EEE, MMM d, yyyy%
%ICUDateTimeFor%TIME(2016, 4, 20+6*7)%EEE, MMM d, yyyy%

date → string
// intYear → intMonth → intDay → string
%ICUDateTimeFor% TIME(2015, 8, 23) %EEE, MMM d, yyyy%

Keep in mind that TIME is in GMT and the output of ICUDateTimeFor is in local time, so when doing this sort of thing it is best to use midday instead of midnight:

%ICUDateTimeFor% TIME(2015, 8, 23,12,0,0) %EEE, MMM d, yyyy%

which will generally work anywhere since at midday GMT, the entire world (almost) is on the same date. Otherwise, folks with negative GMT offsets (which includes all of the Americas for example) will get the wrong result.

and string → date ?

Keyboard Maestro does not have any inbuilt date string parsing facilities.

2 Likes

Thanks Peter, I think that’s a great start.
We can work together to clean it up and complete.

Thank you – that's very helpful – I'll make those edits.

UPDATE

Done. Edits made to the original post.

(and in case any of them are useful – Markdown, iThoughtsX, and OPML versions)

kmDateTimeNotes-002.zip (487.5 KB)

2 Likes

I came across this post and was surprised by the date values shown by my system (macOS Sierra Version 10.12)! I'm posting this on Monday, 17-Oct-16 6:17 PM. Do I have something set up incorrectly such that tomorrow's date and the beginning of next month is not shown in my variables as presented in Peter's examples?

%ICUDateTimeFor% TIME(2016, 10, 18) %EEEE, MMM d, yyyy%

%ICUDateTimeFor%TIME(YEAR(), MONTH()+1, 1)%EEE, MMM d, yyyy%

Keyboard Maestro “ICU Dates Test” Macro

ICU Dates Test.kmmacros (2.2 KB)

Keep in mind that many of the ICU Date/Time functions use GMT, not local time.
See

So I believe that @peternlewis generally recommends that if you set a date using a function like TIME(), that you include a time of noon:
TIME(2016, 10, 18, 12, 0, 0)

Keep in mind that TIME is in GMT and the output of %ICUDateTimeFor% is in local time, so when doing this sort of thing it is best to use midday instead of midnight:

%ICUDateTimeFor% TIME(2015, 8, 23,12,0,0) %EEE, MMM d, yyyy%

which will generally work anywhere since at midday GMT, the entire world (almost) is on the same date. Otherwise, folks with negative GMT offsets (which includes all of the Americas for example) will get the wrong result.

1 Like

To see better, add the time to the date format in your examples (for example MMM d, yyyy HH:mm):

%ICUDateTimePlus%1%Days% adds 1 day (24h) in local time.
%ICUDateTimeFor% sets it to 00:00 GMT and then converts it to local time.

Quite a difference!! I clearly didn’t appreciate the time zone and GMT difference when looking at the examples shown.

Thanks for your help.

I am trying to use the time in this format from Numbers SpreadSheet A (it is text):

2016-05-01 10:02:32 -0500

To match the creation time in Numbers SpreadsheetB (formatted value):

5/1/2016 15:02

I did some regex to harmonize the date piece, extracted the offset in hours and then added the hour offset to the hours of the original date.

Which worked great until I hit hour 25 and/or day 32.

I’ve done a lot of searching on KM and AppleScript to find an way to add a time offset to an anchor date. AppleScript seems basically impossible to do it and account for day, month and year changes.

KM seemed better but I can’t just figure out how to use the tokens in KM.

I thought maybe I could even have Numbers via AppleScript to add/subtract x seconds to the value in SpreadSheet B but I can’t even get that in epoch seconds.

I don’t care, in the end, what time zone the output from the offset is I just need to be able to extract the corrected date and time to do the match. But I see that a lot of the KM calculations end up starting with GMT and ending up local so Peter recommends using 12:00.

But I need to have the day/month/year flip as necessary so I can do the match.

(I hate dates)

BTW, I saw the great example macros for adding a day to a date or time to hh:mm:ss but not one that does the whole shebang where you set a date/time and add an offsetting amount of days/months/years and/or hours/minutes/seconds.

That would be awesome.

I’m not sure which version of Discourse included this, but now if you or @ComplexPoint adjust the original topic post to “wiki,” then anyone can update the information presented therein.