Regular Expression (RegEx) to Filter by Date

regex
jxa

#1

Hello!

I am wondering how to filter dates and time using RegEx / KM

I have a list of dates and time and willing to filter the list with date and time for past 24 hours and 7 days from now:

20/12/2018 01:09
20/12/2018 01:05
19/12/2018 03:09
20/11/2018 01:09

Any ideas?


#2

If those dates are in the format expected by macOS (Sys Prefs > Language and Region), then one option is to filter the dates in a KM Execute JavaScript for Automation action, broadly in this kind of pattern, though you might want to print out the dates in a different format:

Date filtering in an Execute Javascript action.kmmacros (20.7 KB)


Result

Draft JS Source

(() => {
    'use strict';

    const main = () => {

        const
            strDates = Application('Keyboard Maestro Engine')
            .getvariable('dateTimes'),
            xs = map(
                x => new Date(x),
                lines(strDates)
            ),
            dteNow = new Date(),
            dte24Hago = (new Date()).setDate(
                dteNow.getDate() - 1
            ),
            dte7Dhence = (new Date()).setDate(
                dteNow.getDate() + 7
            );

        const
            past24Hs = filter(
                dte => dte >= dte24Hago,
                xs
            ),
            next7Ds = filter(
                dte => dte <= dte7Dhence,
                xs
            );

        return `Last 24 hours:
${past24Hs.join('\n')}

Next 7 days:
${next7Ds.join('\n')}`
    };

    // GENERIC FUNCTIONS ----------------------------
    // https://github.com/RobTrew/prelude-jxa

    // filter :: (a -> Bool) -> [a] -> [a]
    const filter = (f, xs) => xs.filter(f);

    // lines :: String -> [String]
    const lines = s => s.split(/[\r\n]/);

    // map :: (a -> b) -> [a] -> [b]
    const map = (f, xs) =>
        (Array.isArray(xs) ? (
            xs
        ) : xs.split('')).map(f);

    // MAIN ---
    return main();
})();


#3

There is an issue with the date format, in preferences it is set to dd/mm/yy

The presented macro above only works in case the string is in different format - mm/dd/yy

How can this be fixed?

And nne more thing that i want to do after solving the problem with the date format:
If strings will contain sum and currency, is it possible to filter dates and keep sum and currency for every string?

Like this:

20/12/2018 01:09 100 USD
20/12/2018 01:05 100 EUR
19/12/2018 03:09 100 USD
20/11/2018 01:09 100 EUR

The next step that i want to do is to filter dates and get total sum presented next to these dates (24 hours and last 7 days) what can be easily filtered with regex and then calculated.

The final result should look like:
Last 24 hours:

Thu Dec 20 2018 01:09:00 GMT+0200 (EET) 100 USD

Thu Dec 20 2018 01:05:00 GMT+0200 (EET) 100 USD

Total Sum for last 24 hours:
200 USD

And the same for last 7 days.


#4

Second draft, using regexes for that date and currency amount pattern:

(there are, of course, non-JS routes as well, once you are using regexes)

Regex processing and Date filtering in an Execute Javascript action.kmmacros (22.1 KB)

If you are going to consolidate different currencies into one sum then you will need to deal with exchange rates, of course :slight_smile:

tidied

JS second draft

(() => {
    'use strict';

    const main = () => {
        const
            rgxDateVal = /(\d+)\/(\d+)\/(\d+)\s+(\d+)\:(\d+)\s+(\d+)\s+(\w+)/,
            strData = Application('Keyboard Maestro Engine')
            .getvariable('dateTimeValues'),
            xs = concatMap(
                x => {
                    const m = rgxDateVal.exec(x);
                    return Boolean(m) ? [({
                        // y, m, d, h, m, s
                        dateTime: new Date(
                            ...[m[3], m[2] - 1, m[1], m[4], m[5]]
                            .map(s => parseInt(s, 10))
                        ),
                        amount: parseInt(m[6], 10),
                        currency: m[7]
                    })] : [];
                },
                lines(strData)
            );
        const
            dteNow = new Date(),
            dte24Hago = (new Date()).setDate(
                dteNow.getDate() - 1
            ),
            dte7Daysago = (new Date()).setDate(
                dteNow.getDate() - 7
            ),
            last24Hs = filter(
                x => x.dateTime >= dte24Hago,
                xs
            ),
            last7Ds = filter(
                x => x.dateTime >= dte7Daysago,
                xs
            );

        // periodSums :: [Dict] -> String
        const periodSums = events =>
            unlines(map(
                c => foldl(
                    (a, x) => c !== x.currency ? (
                        a
                    ) : a + x.amount,
                    0,
                    events
                ).toString() + ' ' + c,
                nub(map(
                    x => x.currency,
                    events
                ))
            ));
            
        return `
Last 24 hours:
${unlines(map(
    x => x.dateTime.toString() + ' ' + x.amount + ' ' + x.currency,
    last24Hs
))}

Total Sums for last 24 hours:
${periodSums(last24Hs)}

Last 7 days:
${unlines(map(
    x => x.dateTime.toString() + ' ' + x.amount + ' ' + x.currency,
    last7Ds
))}

Total Sums for last 7 days:
${periodSums(last7Ds)}
`
    };

    // GENERIC FUNCTIONS ----------------------------
    // https://github.com/RobTrew/prelude-jxa

    // concatMap :: (a -> [b]) -> [a] -> [b]
    const concatMap = (f, xs) =>
        xs.reduce((a, x) => a.concat(f(x)), []);

    // eq (==) :: Eq a => a -> a -> Bool
    const eq = (a, b) => {
        const t = typeof a;
        return t !== typeof b ? (
            false
        ) : 'object' !== t ? (
            'function' !== t ? (
                a === b
            ) : a.toString() === b.toString()
        ) : (() => {
            const aks = Object.keys(a);
            return aks.length !== Object.keys(b).length ? (
                false
            ) : aks.every(k => eq(a[k], b[k]));
        })();
    };

    // filter :: (a -> Bool) -> [a] -> [a]
    const filter = (f, xs) => xs.filter(f);

    // foldl :: (a -> b -> a) -> a -> [b] -> a
    const foldl = (f, a, xs) => xs.reduce(f, a);

    // lines :: String -> [String]
    const lines = s => s.split(/[\r\n]/);

    // map :: (a -> b) -> [a] -> [b]
    const map = (f, xs) =>
        (Array.isArray(xs) ? (
            xs
        ) : xs.split('')).map(f);

    // nub :: [a] -> [a]
    const nub = xs => nubBy(eq, xs);

    // nubBy :: (a -> a -> Bool) -> [a] -> [a]
    const nubBy = (p, xs) => {
        const go = xs => 0 < xs.length ? (() => {
            const x = xs[0];
            return [x].concat(
                go(xs.slice(1)
                    .filter(y => !p(x, y))
                )
            )
        })() : [];
        return go(xs);
    };

    // unlines :: [String] -> String
    const unlines = xs => xs.join('\n');

    // MAIN ---
    return main();
})();

Search dates and amounts
#5

Awesome, thanks a lot for the help!


#6

Hey @ComplexPoint

One more thing about the previously discussed task.
How can i sum numbers with decimals?

Thanks in advance.


#7

From horseback, does it suffice, experimentally, if you replace parseInt in the code with parseFloat ?

(The former converts the string to an integer - the latter should convert any string with trailing decimals to a floating point value)


#8

It worked!
Much appreciated!


#9

Hey @ComplexPoint!

One more thing to add...how to get the earliest date from the list?


#10

Could you show us a typical sample of input and desired output ?

In the meanwhile, here is a first guess:

(() => {
    'use strict';

    const main = () => {
        const
            rgxDateVal = /(\d+)\/(\d+)\/(\d+)\s+(\d+)\:(\d+)\s+(\d+)\s+(\w+)/,
            strData = Application('Keyboard Maestro Engine')
            .getvariable('dateTimeValues'),
            xs = concatMap(
                x => {
                    const m = rgxDateVal.exec(x);
                    return Boolean(m) ? [({
                        // y, m, d, h, m, s
                        dateTime: new Date(
                            ...[m[3], m[2] - 1, m[1], m[4], m[5]]
                            .map(s => parseFloat(s, 10))
                        ),
                        amount: parseFloat(m[6], 10),
                        currency: m[7]
                    })] : [];
                },
                lines(strData)
            );
        const
            dteNow = new Date(),
            dte24Hago = (new Date()).setDate(
                dteNow.getDate() - 1
            ),
            dte7Daysago = (new Date()).setDate(
                dteNow.getDate() - 7
            ),
            last24Hs = filter(
                x => x.dateTime >= dte24Hago,
                xs
            ),
            last7Ds = filter(
                x => x.dateTime >= dte7Daysago,
                xs
            );

        // periodSums :: [Dict] -> String
        const periodSums = events =>
            unlines(map(
                c => foldl(
                    (a, x) => c !== x.currency ? (
                        a
                    ) : a + x.amount,
                    0,
                    events
                ).toString() + ' ' + c,
                nub(map(
                    x => x.currency,
                    events
                ))
            ));

        // dateSortedAmounts :: [Dict] -> [Dict]
        const dateSortedAmounts = xs =>
            sortBy(comparing(x => x.dateTime), xs);


        return `
Last 24 hours:
${unlines(map(
    x => x.dateTime.toString() + ' ' + x.amount + ' ' + x.currency,
    last24Hs
))}

Total Sums for last 24 hours:
${periodSums(last24Hs)}

Last 7 days:
${unlines(map(
    x => x.dateTime.toString() + ' ' + x.amount + ' ' + x.currency,
    last7Ds
))}

Total Sums for last 7 days:
${periodSums(last7Ds)}

Earliest amount in data:
${
    0 < xs.length ? (() => {
        const x = dateSortedAmounts(xs)[0];
        return x.dateTime.toString() + ' ' + x.amount + ' ' + x.currency
    })() : 'None in range'
}

`
    };

    // GENERIC FUNCTIONS ----------------------------
    // https://github.com/RobTrew/prelude-jxa

    // comparing :: (a -> b) -> (a -> a -> Ordering)
    const comparing = f =>
        (x, y) => {
            const
                a = f(x),
                b = f(y);
            return a < b ? -1 : (a > b ? 1 : 0);
        };

    // concatMap :: (a -> [b]) -> [a] -> [b]
    const concatMap = (f, xs) =>
        xs.reduce((a, x) => a.concat(f(x)), []);

    // eq (==) :: Eq a => a -> a -> Bool
    const eq = (a, b) => {
        const t = typeof a;
        return t !== typeof b ? (
            false
        ) : 'object' !== t ? (
            'function' !== t ? (
                a === b
            ) : a.toString() === b.toString()
        ) : (() => {
            const aks = Object.keys(a);
            return aks.length !== Object.keys(b).length ? (
                false
            ) : aks.every(k => eq(a[k], b[k]));
        })();
    };

    // filter :: (a -> Bool) -> [a] -> [a]
    const filter = (f, xs) => xs.filter(f);

    // foldl :: (a -> b -> a) -> a -> [b] -> a
    const foldl = (f, a, xs) => xs.reduce(f, a);

    // lines :: String -> [String]
    const lines = s => s.split(/[\r\n]/);

    // map :: (a -> b) -> [a] -> [b]
    const map = (f, xs) =>
        (Array.isArray(xs) ? (
            xs
        ) : xs.split('')).map(f);

    // nub :: [a] -> [a]
    const nub = xs => nubBy(eq, xs);

    // nubBy :: (a -> a -> Bool) -> [a] -> [a]
    const nubBy = (p, xs) => {
        const go = xs => 0 < xs.length ? (() => {
            const x = xs[0];
            return [x].concat(
                go(xs.slice(1)
                    .filter(y => !p(x, y))
                )
            )
        })() : [];
        return go(xs);
    };

    // sortBy :: (a -> a -> Ordering) -> [a] -> [a]
    const sortBy = (f, xs) =>
        xs.slice()
        .sort(f);

    // unlines :: [String] -> String
    const unlines = xs => xs.join('\n');

    // MAIN ---
    return main();
})();


#11

You don't need examples of input and output. The provided script is more than okay, it does the job.
Once again many thanks :+1::+1::+1:


#12

Hey @ComplexPoint,

Sorry for bothering you again :slight_smile:
How can i change the date format to yy/mm/dd instead of printing in out like "Wed Mar 13 2019"? Tried doing it myself but failed.
Please advise.


#13

The first experiment would be to rewrite all datevalue.toString() expressions as datevalue.toLocaleString()

as in something like:

(() => {
    'use strict';

    const main = () => {
        const
            rgxDateVal = /(\d+)\/(\d+)\/(\d+)\s+(\d+)\:(\d+)\s+(\d+)\s+(\w+)/,
            strData = Application('Keyboard Maestro Engine')
            .getvariable('dateTimeValues'),
            xs = concatMap(
                x => {
                    const m = rgxDateVal.exec(x);
                    return Boolean(m) ? [({
                        // y, m, d, h, m, s
                        dateTime: new Date(
                            ...[m[3], m[2] - 1, m[1], m[4], m[5]]
                            .map(s => parseFloat(s, 10))
                        ),
                        amount: parseFloat(m[6], 10),
                        currency: m[7]
                    })] : [];
                },
                lines(strData)
            );
        const
            dteNow = new Date(),
            dte24Hago = (new Date()).setDate(
                dteNow.getDate() - 1
            ),
            dte7Daysago = (new Date()).setDate(
                dteNow.getDate() - 7
            ),
            last24Hs = filter(
                x => x.dateTime >= dte24Hago,
                xs
            ),
            last7Ds = filter(
                x => x.dateTime >= dte7Daysago,
                xs
            );

        // periodSums :: [Dict] -> String
        const periodSums = events =>
            unlines(map(
                c => foldl(
                    (a, x) => c !== x.currency ? (
                        a
                    ) : a + x.amount,
                    0,
                    events
                ).toString() + ' ' + c,
                nub(map(
                    x => x.currency,
                    events
                ))
            ));

        // dateSortedAmounts :: [Dict] -> [Dict]
        const dateSortedAmounts = xs =>
            sortBy(comparing(x => x.dateTime), xs);


        return `
Last 24 hours:
${unlines(map(
    x => x.dateTime.toLocaleDateString() + ' ' + x.amount + ' ' + x.currency,
    last24Hs
))}

Total Sums for last 24 hours:
${periodSums(last24Hs)}

Last 7 days:
${unlines(map(
    x => x.dateTime.toLocaleDateString() + ' ' + x.amount + ' ' + x.currency,
    last7Ds
))}

Total Sums for last 7 days:
${periodSums(last7Ds)}

Earliest amount in data:
${
    0 < xs.length ? (() => {
        const x = dateSortedAmounts(xs)[0];
        return x.dateTime.toLocaleDateString() +
            ' ' + x.amount + ' ' + x.currency
    })() : 'None in range'
}

`
    };

    // GENERIC FUNCTIONS ----------------------------
    // https://github.com/RobTrew/prelude-jxa

    // comparing :: (a -> b) -> (a -> a -> Ordering)
    const comparing = f =>
        (x, y) => {
            const
                a = f(x),
                b = f(y);
            return a < b ? -1 : (a > b ? 1 : 0);
        };

    // concatMap :: (a -> [b]) -> [a] -> [b]
    const concatMap = (f, xs) =>
        xs.reduce((a, x) => a.concat(f(x)), []);

    // eq (==) :: Eq a => a -> a -> Bool
    const eq = (a, b) => {
        const t = typeof a;
        return t !== typeof b ? (
            false
        ) : 'object' !== t ? (
            'function' !== t ? (
                a === b
            ) : a.toString() === b.toString()
        ) : (() => {
            const aks = Object.keys(a);
            return aks.length !== Object.keys(b).length ? (
                false
            ) : aks.every(k => eq(a[k], b[k]));
        })();
    };

    // filter :: (a -> Bool) -> [a] -> [a]
    const filter = (f, xs) => xs.filter(f);

    // foldl :: (a -> b -> a) -> a -> [b] -> a
    const foldl = (f, a, xs) => xs.reduce(f, a);

    // lines :: String -> [String]
    const lines = s => s.split(/[\r\n]/);

    // map :: (a -> b) -> [a] -> [b]
    const map = (f, xs) =>
        (Array.isArray(xs) ? (
            xs
        ) : xs.split('')).map(f);

    // nub :: [a] -> [a]
    const nub = xs => nubBy(eq, xs);

    // nubBy :: (a -> a -> Bool) -> [a] -> [a]
    const nubBy = (p, xs) => {
        const go = xs => 0 < xs.length ? (() => {
            const x = xs[0];
            return [x].concat(
                go(xs.slice(1)
                    .filter(y => !p(x, y))
                )
            )
        })() : [];
        return go(xs);
    };

    // sortBy :: (a -> a -> Ordering) -> [a] -> [a]
    const sortBy = (f, xs) =>
        xs.slice()
        .sort(f);

    // unlines :: [String] -> String
    const unlines = xs => xs.join('\n');

    // MAIN ---
    return main();
})();


#14

After that, for any refinements, you could play around with the options listed at:

(Including the options examples in the section towards the bottom of the page.)

Using_options