Get a Date X Days in the Past or Future using GNU Date

Written by James McDonald

February 20, 2017

Recently was running a Pentaho Data Integration that had an input parameter of yesterdays date. I was getting the date inside mysql or inside a javascript step but wanted to be able to pass in an arbitrary date so I could run the report for any day.

So on Linux with GNU Date you can run:

date --date="1 day ago"
Sun Feb 19 10:03:38 AEDT 2017

However on Mac trying the same causes a complaint:

date --date="1 day ago" 
date: illegal option -- -
usage: date [-jnRu] [-d dst] [-r seconds] [-t west] [-v[+|-]val[ymwdHMS]] ... 
            [-f fmt date | [[[mm]dd]HH]MM[[cc]yy][.ss]] [+format]

The work around is to install coreutils using Homebrew and then use gdate (actually I think coreutils may be part of the default Homebrew install so you may just have to install Homebrew but if not after Homebrew is installed just run brew install coreutils)

gdate --date="5 days ago"
Wed Feb 15 10:09:36 AEDT 2017

There are a heap of options you can give to –date in human readable format 

date --date="20 days"
Sun Mar 12 10:30:18 AEDT 2017

date --date="1 year"
Tue Feb 20 10:30:24 AEDT 2018

date --date="365 days"
Tue Feb 20 10:30:32 AEDT 2018

And finally you may need to format your date to make it MySQL database query friendly so:

gdate --date="5 days ago" +%Y-%m-%d
2017-02-15

So in the example of the Pentaho Job you can then create a script to pass the parameter into the Job/Transformation/Report as follows

#!/bin/bash

DIR="$( cd "$( dirname "${BASH_SOURCE[0]}" )" && pwd )"

if [ -f "/Applications/pentaho/data-integration/kitchen.sh" ]
then
	# mac
	KITCHEN=/Applications/pentaho/data-integration/kitchen.sh
	PENTAHO_SRC=$DIR
	LDATE=/usr/local/bin/gdate
else 
	# linux
	LDATE=/bin/date
	PENTAHO_ROOT=/home/pentahouser/pentaho
	PENTAHO_SRC=${PENTAHO_ROOT}/src
	PDI=${PENTAHO_ROOT}/data-integration

fi

if [ -z "$1" ];
then

ONE_DAY_AGO=`$LDATE --date="1 days ago" +%Y-%m-%d`

else 

ONE_DAY_AGO="$1"

fi
KITCHEN=${PDI}/kitchen.sh

KJB=${PENTAHO_SRC}/dsr_onepage_nodetail.kjb

echo Looking at $ONE_DAY_AGO 
$KITCHEN -file=${KJB} -param date_now="$ONE_DAY_AGO"


0 Comments

Submit a Comment

Your email address will not be published. Required fields are marked *

This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

The reCAPTCHA verification period has expired. Please reload the page.

You May Also Like…

Squarespace Image Export

To gain continued access to your Squarespace website images after cancelling your subscription you have several...

MySQL 8.x GRANT ALL STATEMENT

-- CREATE CREATE USER 'tgnrestoreuser'@'localhost' IDENTIFIED BY 'AppleSauceLoveBird2024'; GRANT ALL PRIVILEGES ON...

Exetel Opt-Out of CGNAT

If your port forwards and inbound and/or outbound site-to-site VPN's have failed when switching to Exetel due to their...