How to truncate datetime object and get the date in Python
datetime object holds both date and time values. But, sometimes we need to show only the date without any time values hour, minute, seconds.
In this post, we will learn how to truncate the time from a datetime object in Python with example.
date() method of datetime module returns only the date component of datetime, excluding time part. We can use this method to print the date of a datetime.
from datetime import datetime now = datetime.now() print(now.date())It will print something like below:
Method 2: By using strftime():
strftime takes one string format and it formats the datetime object based on that. We can pass one string that includes only day, month and year to get the required result:
from datetime import datetime now = datetime.now() print(now.strftime("%d/%m/%Y"))The advantage of this method is that we can change the format of the output string to whatever we like.
Similarly, if you want the month in words, you can do something as like below:
from datetime import datetime now = datetime.now() print(now.strftime("%d/%b/%Y"))or if you want the month in full:
from datetime import datetime now = datetime.now() print(now.strftime("%d/%B/%Y"))Method 3: By reading the properties:
datetime object has day, month and year properties. We can read these properties to create a date string with day, month and year.
from datetime import datetime now = datetime.now() print(f'now.day>::now.month>::now.year>')It will print something like below:
pyspark.sql.functions.date_trunc¶
Returns timestamp truncated to the unit specified by the format.
Changed in version 3.4.0: Supports Spark Connect.
‘year’, ‘yyyy’, ‘yy’ to truncate by year, ‘month’, ‘mon’, ‘mm’ to truncate by month, ‘day’, ‘dd’ to truncate by day, Other options are: ‘microsecond’, ‘millisecond’, ‘second’, ‘minute’, ‘hour’, ‘week’, ‘quarter’
timestamp Column or str
input column of values to truncate.
>>> df = spark.createDataFrame([('1997-02-28 05:02:11',)], ['t']) >>> df.select(date_trunc('year', df.t).alias('year')).collect() [Row(year=datetime.datetime(1997, 1, 1, 0, 0))] >>> df.select(date_trunc('mon', df.t).alias('month')).collect() [Row(month=datetime.datetime(1997, 2, 1, 0, 0))]
© Copyright .
Created using Sphinx 3.0.4.
datetime-truncate 1.1.1
This module truncates a datetime object to the level of precision that you specify, making everything higher than that zero (or one for day and month).
It is based on PostgreSQL’s DATE_TRUNC.
Documentation available on Read the Docs.
Installation:
You can install from pypi!
pip install datetime_truncateUsage:
>>> from datetime_truncate import truncate >>> truncate(datetime(2012, 2, 4, 12, 24, 50, 234), 'second') datetime(2012, 2, 4, 12, 24, 50) >>> truncate(datetime(2012, 2, 4, 12, 24, 50), 'minute') datetime(2012, 2, 4, 12, 24) >>> truncate(datetime(2012, 2, 4, 12, 24), 'hour') datetime(2012, 2, 4, 12) >>> truncate(datetime(2012, 2, 4, 12, 24), 'day') datetime(2012, 2, 4) >>> truncate(datetime(2012, 2, 4, 12, 24), 'week') datetime(2012, 1, 30) >>> truncate(datetime(2012, 2, 4, 12, 24), 'month') datetime(2012, 2, 1) >>> truncate(datetime(2012, 2, 4, 12, 24), 'quarter') datetime(2012, 1, 1) >>> truncate(datetime(2012, 8, 18, 12, 25), 'half_year') datetime(2012, 7, 1) >>> truncate(datetime(2012, 8, 18, 12, 25), 'year') datetime(2012, 1, 1)There are also sugar functions available on the form:
- truncate_second
- truncate_minute
- truncate_hour
- truncate_day
- truncate_week
- truncate_month
- truncate_quarter
- truncate_half_year
- truncate_year
Using Date and Time Trunc Functions¶
In Data Warehousing we quite often run to date reports such as week to date, month to date, year to date etc. Let us understand how we can take care of such requirements using appropriate functions over Spark Data Frames.
- We can use trunc or date_trunc for the same to get the beginning date of the week, month, current year etc by passing date or timestamp to it.
- We can use trunc to get beginning date of the month or year by passing date or timestamp to it — for example trunc(current_date(), «MM») will give the first of the current month.
- We can use date_trunc to get beginning date of the month or year as well as beginning time of the day or hour by passing timestamp to it.
- Get beginning date based on month — date_trunc(«MM», current_timestamp())
- Get beginning time based on day — date_trunc(«DAY», current_timestamp())
Let us start spark context for this Notebook so that we can execute the code provided. You can sign up for our 10 node state of the art cluster/labs to learn Spark SQL using our unique integrated LMS.
from pyspark.sql import SparkSession import getpass username = getpass.getuser() spark = SparkSession. \ builder. \ config('spark.ui.port', '0'). \ config("spark.sql.warehouse.dir", f"/user/username>/warehouse"). \ enableHiveSupport(). \ appName(f'username> | Python - Processing Column Data'). \ master('yarn'). \ getOrCreate()
If you are going to use CLIs, you can use Spark SQL using one of the 3 approaches.
Using Spark SQL
spark2-sql \ --master yarn \ --conf spark.ui.port=0 \ --conf spark.sql.warehouse.dir=/user/$/warehouse
Using Scala
spark2-shell \ --master yarn \ --conf spark.ui.port=0 \ --conf spark.sql.warehouse.dir=/user/$/warehouse
Using Pyspark
pyspark2 \ --master yarn \ --conf spark.ui.port=0 \ --conf spark.sql.warehouse.dir=/user/$/warehouse
from pyspark.sql.functions import trunc, date_trunc
Tasks¶
Let us perform few tasks to understand trunc and date_trunc in detail.
datetimes = [("2014-02-28", "2014-02-28 10:00:00.123"), ("2016-02-29", "2016-02-29 08:08:08.999"), ("2017-10-31", "2017-12-31 11:59:59.123"), ("2019-11-30", "2019-08-31 00:00:00.000") ]
datetimesDF = spark.createDataFrame(datetimes, schema="date STRING, time STRING")
datetimesDF.show(truncate=False)
+----------+-----------------------+ |date |time | +----------+-----------------------+ |2014-02-28|2014-02-28 10:00:00.123| |2016-02-29|2016-02-29 08:08:08.999| |2017-10-31|2017-12-31 11:59:59.123| |2019-11-30|2019-08-31 00:00:00.000| +----------+-----------------------+
from pyspark.sql.functions import trunc
datetimesDF. \ withColumn("date_trunc", trunc("date", "MM")). \ withColumn("time_trunc", trunc("time", "yy")). \ show(truncate=False)
+----------+-----------------------+----------+----------+ |date |time |date_trunc|time_trunc| +----------+-----------------------+----------+----------+ |2014-02-28|2014-02-28 10:00:00.123|2014-02-01|2014-01-01| |2016-02-29|2016-02-29 08:08:08.999|2016-02-01|2016-01-01| |2017-10-31|2017-12-31 11:59:59.123|2017-10-01|2017-01-01| |2019-11-30|2019-08-31 00:00:00.000|2019-11-01|2019-01-01| +----------+-----------------------+----------+----------+
from pyspark.sql.functions import date_trunc
datetimesDF. \ withColumn("date_trunc", date_trunc('MM', "date")). \ withColumn("time_trunc", date_trunc('yy', "time")). \ show(truncate=False)
+----------+-----------------------+-------------------+-------------------+ |date |time |date_trunc |time_trunc | +----------+-----------------------+-------------------+-------------------+ |2014-02-28|2014-02-28 10:00:00.123|2014-02-01 00:00:00|2014-01-01 00:00:00| |2016-02-29|2016-02-29 08:08:08.999|2016-02-01 00:00:00|2016-01-01 00:00:00| |2017-10-31|2017-12-31 11:59:59.123|2017-10-01 00:00:00|2017-01-01 00:00:00| |2019-11-30|2019-08-31 00:00:00.000|2019-11-01 00:00:00|2019-01-01 00:00:00| +----------+-----------------------+-------------------+-------------------+
datetimesDF. \ withColumn("date_dt", date_trunc("HOUR", "date")). \ withColumn("time_dt", date_trunc("HOUR", "time")). \ withColumn("time_dt1", date_trunc("dd", "time")). \ show(truncate=False)
+----------+-----------------------+-------------------+-------------------+-------------------+ |date |time |date_dt |time_dt |time_dt1 | +----------+-----------------------+-------------------+-------------------+-------------------+ |2014-02-28|2014-02-28 10:00:00.123|2014-02-28 00:00:00|2014-02-28 10:00:00|2014-02-28 00:00:00| |2016-02-29|2016-02-29 08:08:08.999|2016-02-29 00:00:00|2016-02-29 08:00:00|2016-02-29 00:00:00| |2017-10-31|2017-12-31 11:59:59.123|2017-10-31 00:00:00|2017-12-31 11:00:00|2017-12-31 00:00:00| |2019-11-30|2019-08-31 00:00:00.000|2019-11-30 00:00:00|2019-08-31 00:00:00|2019-08-31 00:00:00| +----------+-----------------------+-------------------+-------------------+-------------------+