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').
- %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!
Comments
0 comments
Article is closed for comments.