Using existing date/time string as basis of calculation?

Hello,

I'm working on a macro to process a huge set of excel info, and I'm having a hard time figuring out how to work with date and time formatted variables.

Here is one existing datum:

2020:09:23 11:53:54

Basically, I would like to interpret every subsequent date and time as a difference from that date. So...

2020:09:23 11:53:54

Against,

2020:09:23 11:55:01

Becomes

0000:00:00 00:01:07

I'm assuming the easiest way to do that is interpreting the existing string (which was raw data from exiftool and not formatted to play nicely with execl's dates) as time information through KM, but I'm unsure how to proceed.

Quite honestly, one way I was thinking about doing this would just be to reduce the dates and times to seconds, and then calculate the difference in seconds and then create a segmented counter that notches to the next digit at 60, 60, 24, 31, etc. Is there a cleaner way of doing this?

Any help would be greatly appreciated!

I'm not sure how these exif difference strings would be defined if the gap was more than 28 days – (you would then get into the calendrical issues of how many days are contained in each month).

But if the set of gaps you are dealing with is smaller than 4 weeks, then you could try defining (in JS or AppleScript) something like this:

secondsFromExifDateString :: String -> Int 

(Mapping a given Exif string onto an integer number of seconds)

and another function of this type:

exifStringFromSeconds :: Int -> String

The only constraint of this approach is that it's undefined beyond a range of four weeks, after which we begin to need to know exactly what month and day (and in late February, even which year) we are dealing with.

EXIF String -> EXIF String -> Int

is, of course a 'lossy' conversion (we end up with a definite number of seconds between the two date-times, but we leave behind the context of a particular moment in a calendar).

Given those caveats and limits, if you chose an Execute JavaScript action in KM, you could write something like this for gaps below four weeks:

EXIF date gap string.kmmacros (25.0 KB)

JS Source
(() => {
    'use strict';

    // ------ EXIF DELTA STRING FROM TWO EXIF DATES ------

    // Rob Trew @ 2020
    // Ver 0.01

    // main :: IO ()
    const main = () => {
        const
            kme = Application('Keyboard Maestro Engine'),
            kmVar = kme.getvariable;
        return either(
            alert('Exif seconds string')
        )(
            x => x
        )(
            exifStringFromSecondsLR(
                secondsFromExifDateString(
                    kmVar('exifB')
                ) - (
                    secondsFromExifDateString(
                        kmVar('exifA')
                    )
                )
            )
        );
    };

    // exifStringFromSecondsLR :: Int -> 
    // Either String String
    const exifStringFromSecondsLR = n =>
        // A string in the format 2020:09:23 11:53:54,
        // representing an absolute number of seconds.
        //
        // Only defined for the range [1..2419200]
        // where 2419200 == 28 days * 24h * 60m * 60s.
        n > 2419200 ? (
            Left(`Beyond 4 week range: ${n} seconds.`)
        ) : (
            Right((() => {
                const
                    padded = n => n.toString()
                    .padStart(2, '0');
                const [d, h, m, s] = snd(
                    mapAccumR(quotRem)(n)([
                        28, // 4 weeks
                        24, // 1 day
                        60, // 1 hour
                        60 // 1 minute
                    ])
                );
                return `0000:00:${padded(d)} ` + (
                    map(padded)([h, m, s]).join(':')
                );
            })())
        )

    // secondsFromExifDateString :: String -> Int 
    const secondsFromExifDateString = s =>
        zip([
            'FullYear', 'Month', 'Date',
            'Hours', 'Minutes', 'Seconds',
            'Milliseconds'
        ])(
            map(Number)(
                words(s).flatMap(
                    x => x.split(':')
                ).concat('0')
            )
        ).reduce(updated, new Date()) / 1000;

    // updated :: Date -> (String, Int) -> Date
    const updated = (date, kv) => {
        const dte = new Date(date);
        return (
            dte['set' + fst(kv)](snd(kv)),
            dte
        );
    }

    // --------------------- JXA ---------------------

    // alert :: String => String -> IO String
    const alert = title =>
        s => {
            const sa = Object.assign(
                Application('System Events'), {
                    includeStandardAdditions: true
                });
            return (
                sa.activate(),
                sa.displayDialog(s, {
                    withTitle: title,
                    buttons: ['OK'],
                    defaultButton: 'OK'
                }),
                s
            );
        };

    // ------------------- JS PRELUDE -------------------
    // https://github.com/RobTrew/prelude-jxa

    // Left :: a -> Either a b
    const Left = x => ({
        type: 'Either',
        Left: x
    });


    // Right :: b -> Either a b
    const Right = x => ({
        type: 'Either',
        Right: x
    });


    // Tuple (,) :: a -> b -> (a, b)
    const Tuple = a =>
        b => ({
            type: 'Tuple',
            '0': a,
            '1': b,
            length: 2
        });


    // either :: (a -> c) -> (b -> c) -> Either a b -> c
    const either = fl =>
        // Application of the function fl to the
        // contents of any Left value in e, or
        // the application of fr to its Right value.
        fr => e => 'Either' === e.type ? (
            undefined !== e.Left ? (
                fl(e.Left)
            ) : fr(e.Right)
        ) : undefined;


    // fst :: (a, b) -> a
    const fst = tpl =>
        // First member of a pair.
        tpl[0];


    // length :: [a] -> Int
    const length = xs =>
        // Returns Infinity over objects without finite
        // length. This enables zip and zipWith to choose
        // the shorter argument when one is non-finite,
        // like cycle, repeat etc
        'GeneratorFunction' !== xs.constructor
        .constructor.name ? (
            xs.length
        ) : Infinity;


    // list :: StringOrArrayLike b => b -> [a]
    const list = xs =>
        // xs itself, if it is an Array,
        // or an Array derived from xs.
        Array.isArray(xs) ? (
            xs
        ) : Array.from(xs || []);


    // map :: (a -> b) -> [a] -> [b]
    const map = f =>
        // The list obtained by applying f
        // to each element of xs.
        // (The image of xs under f).
        xs => [...xs].map(f);


    // mapAccumR :: (acc -> x -> (acc, y)) -> acc -> [x] -> (acc, [y])
    const mapAccumR = f =>
        // A tuple of an accumulation and a list 
        // obtained by a combined map and fold,
        // with accumulation from right to left.
        acc => xs => [...xs].reduceRight((a, x) => {
            const pair = f(a[0])(x);
            return Tuple(pair[0])(
                [pair[1]].concat(a[1])
            );
        }, Tuple(acc)([]));


    // quotRem :: Int -> Int -> (Int, Int)
    const quotRem = m => n =>
        Tuple(Math.trunc(m / n))(
            m % n
        );


    // snd :: (a, b) -> b
    const snd = tpl =>
        // Second member of a pair.
        tpl[1];


    // take :: Int -> [a] -> [a]
    // take :: Int -> String -> String
    const take = n =>
        // The first n elements of a list,
        // string of characters, or stream.
        xs => 'GeneratorFunction' !== xs
        .constructor.constructor.name ? (
            xs.slice(0, n)
        ) : [].concat.apply([], Array.from({
            length: n
        }, () => {
            const x = xs.next();
            return x.done ? [] : [x.value];
        }));


    // words :: String -> [String]
    const words = s =>
        // List of space-delimited sub-strings.
        s.split(/\s+/);


    // zip :: [a] -> [b] -> [(a, b)]
    const zip = xs =>
        // Use of `take` and `length` here allows for 
        // zipping with non-finite lists - i.e. generators 
        // like cycle, repeat, iterate.
        ys => (([xs_, ys_]) => {
            const
                n = Math.min(...[xs_, ys_].map(length)),
                vs = take(n)(ys_);
            return take(n)(xs_).map(
                (x, i) => Tuple(x)(vs[i])
            );
        })([xs, ys].map(list));

    return main();
})();

I found a solution that may not be exceedingly durable for every usage case, but if anyone is curious, here's what I figured out:

You're right that if the gap was greater than 28 days I was going to run into issues. In my original post I talked about reducing it down to seconds and then using a counter from there. In my case, I only wanted hours as the maximum unit, so I don't run into the month problem.

Since the exif tool format for dates was the same, I was able to parse out each field into a separate variable (var_Year, var_Month, var_Day, var_Hour, var_Minute, var_Second) using "Get Substring of Variable" (I knew year would be the first four, so var_Year is defined as get Substring of Variable "input", first 4 characters, the month would be 5 and 6, etc...)

Then using TIME() as TIME(var_Year...) it returned the unix timecode of the first date. I repeated the process for the second, then subtracted the second time from the starting time, and used DIV and MOD to break up the seconds out into hours, minutes, and seconds.

If anyone is interested, I can post the actual KM macros, but as others on this forum have said, date and time calculations seem to be rather convoluted by using only macros. Unfortunately, my JS skills are not quite nimble enough to execute the operation within a code block.

1 Like

Convoluted or not, this is a KM forum after all (as opposed to a JavaScript one) and date-related problems come up regularly so I for one would be very interested in seeing your solution if only for future reference. :pray:

? :slight_smile:

( JS actions are just one part of the ecology )

I'm sure this can be done with KM Actions, but since you are already using Excel would encourage you to make use of Excel's excellent date tools:

image

You just need to replace the colons in the date part with a dash or slash, and then paste into excel.
You will also need to use custom date formats, like this for the elapsed time:
MM:dd hh:mm:ss

If this does not work for you, let us know and we will try to provide more help.

I wasn't sure how to reformat the cells so that Excel would read the date and time, but that makes sense. I have another offset that I needed to add into the process (I'm creating names for 840 files based on the time and a rolling counter added together) so I figured KM would be a little easier to get looking exactly like I wanted it to rather than trying to make excel kinda hacky

Sure, here is some documentation. Vexations is the variable set prefix I'm using for this macro, fyi

1 Like

I really appreciate the time and effort you've invested in sharing this with us. Thank you :+1: :+1:

1 Like