Scripts: Adding, transforming and comparing dates

CHANGING FORMAT OF A DATE COLUMN

To change the date format of a column use the following as expression in your command:

__import__('datetime').datetime.strptime({date_column_name}, '%d.%m.%Y').strftime('%Y-%m-%d')

• %d.%m.%Y in this example is the initial format.
• %Y-%m-%d in this example is the target format.
• See Python documentation for available placeholders.

ADDING YESTERDAY 

__import__('datetime').datetime.today() - __import__('datetime').timedelta(days=1)

ADDING WEEKDAY BASED ON A DATE

__import__('datetime'). datetime.strptime({date_column_name}, '%Y-%m-%d').weekday()

• %Y-%m-%d in this example is the current format of the date column

Optional: 

__import__('datetime').datetime.strptime({date_column_name}, '%Y-%m-%d').strftime('%A-%d-%m-%Y')

• %Y-%m-%d in this example is the current format of the date column.

ADDING WEEK/MONTH/YEAR BASED ON A DATE

To import the week/month/year of an already existing date column:

__import__('datetime').datetime.strftime(__import__('datetime').datetime.strptime({date_column_name},'%Y-%m-%d'), '%W')

• %Y-%m-%d in this example is format of your column.
• %W - is the placeholder for week where Monday is the 1st day of the week. The week preceding the first Monday will be counted as '00'. 
• %V - The ISO 8601 week number of the current year (01 to 53), where week 1 is the first week that has at least 4 days in the current year, and with Monday as the first day of the week.

See Python documentation for other available placeholders

COMPARE TWO DATE COLUMNS

You can compare two columns with date values by using this expression: 

__import__('datetime').datetime.strptime({date_column_name},'%Y-%m-%d') > __import__('datetime').datetime.strptime('2017-07-01','%Y-%m-%d')

• %Y-%m-%d in this example is format of your column
• >,<,==,!= are all allowed operators for comparisons.
• See Python documentation for other available placeholders

 COMPARE A DATE COLUMN TO A RELATIVE DATE

To decrease/increase the value of the date you want to compare your date column to use

__import__('datetime').datetime.strptime({date_column_name},'%Y-%m-%d')  + __import__('datetime').timedelta(days=1)

To compare your date column to the current date, month or year you can use these functions:

__import__('datetime').datetime.today().day

__import__('datetime').datetime.strftime(__import__('datetime').datetime.today(),'%m')

__import__('datetime').datetime.strftime(__import__('datetime').datetime.today(),'%Y')

INCREMENT/DECREMENT TIMESTAMPS

__import__('datetime').datetime.strptime({order.closed_at}, "%Y-%m-%d %H:%M:%S") - __import__('datetime').timedelta(days=0, hours=8, minutes=0)

CONVERT TIMEZONES

__import__('pytz').timezone('UTC').localize(__import__('datetime').datetime.strptime({Date column}, '%Y-%m-%dT%H:%M:%SZ')).astimezone(__import__('pytz').timezone('Europe/Berlin')).strftime('%Y %m %dT%H:%M:%SZ')

• {Date column} - Your column name
• %Y-%m-%dT%H:%M:%SZ - Format of your column.
• %Y %m %dT%H:%M:%SZ - Output format
• UTC - Input timezone
• Europe/Berlin - Output timezone
• To get list of all available timezones, run in python shell command:  __import__('pytz').all_timezones

NUMBER OF DAYS BETWEEN TWO DATES

(__import__('datetime').datetime.strptime({Date_column_11},'%Y-%m-%d') - __import__('datetime').datetime.strptime({DAte_column_2},'%Y-%m-%d')).days

CONVERTING UNIX-TIMESTAMP TO DATE

Expression for changing a unix timestamp into yyyy-mm-dd date format:

__import__('datetime').datetime.fromtimestamp(int({date_column_name})).strftime('%Y-%m-%d')

 __import__('datetime').datetime.utcfromtimestamp({date_column_name}/1000.0).strftime('%Y-%m-%d')

ADD CURRENT DATE

str(__import__('datetime').datetime.strftime(__import__('datetime').datetime.today(),'%Y-%m-%d')) 

ADD DATE BASED ON CALENDAR WEEK

__import__('datetime').datetime.strptime(str({year}) + str({Week}) + '-1', '%Y%W-%w').strftime('%Y-%m-%d')

• %Y-%m-%d in this example is the desired output format
• %w is the placeholder for the day of the week (where 0 is Sunday and 6 is Saturday). In this case we are using Monday (1).
• Calendar Week starts counting from 0!