What you will be able to do
- Pull year, month, day, quarter and time-of-day parts out of a date or timestamp column as integers
- Tell dayofweek numbering (1 = Sunday) apart from weekday numbering (0 = Monday)
- Use extract and date_part with a field name, and read their abbreviated field codes correctly
- Explain why extracting from a TIMESTAMP depends on the session time zone
1.One function per date component
After a column holds a date or timestamp, the usual next step is to break out a component: the year to partition on, the month to aggregate by, or the weekday to filter on. You could format the value with date_format and parse the text, but the date_format reference itself recommends against that: "Whenever possible, use specialized functions like year."
from pyspark.sql import functions as dbf
df = spark.createDataFrame([('2015-04-08',), ('2024-10-31',)], ['dt'])
df.select("*", dbf.typeof('dt'), dbf.year('dt')).show()The same page repeats the call on '2015-04-08 13:08:15' strings and on Python datetime.date and datetime.datetime values. The other extractors work the same way: each takes a single column and returns an integer. Two return text instead: dayname and monthname give three-letter abbreviations.
| Function | What it returns |
|---|---|
| year(col) | Year of a date/timestamp as integer |
| quarter(col) | Quarter of a date/timestamp as integer |
| month(col) | Month of a date/timestamp as integer |
| dayofmonth(col) / day(col) | Day of the month as integer |
| dayofyear(col) | Day of the year as integer |
| weekofyear(col) | Week number of a date as integer |
| hour(col) / minute(col) / second(col) | Hours, minutes, seconds as integer |
| dayname(col) / monthname(col) | Three-letter abbreviated day or month name |
Checkpoint 1 of 5· Check yourself
You need the calendar quarter of an order_date column as a number you can group by. Which choice does the documentation steer you toward?
A dedicated extractor returns the integer directly, and date_format's own reference tells you to prefer those specialized functions.
“Whenever possible, use specialized functions like year.”Source: docs.databricks.com
2.dayofweek and weekday count differently
Two functions return the day of the week as a number, and they use different scales. dayofweek runs from 1 for Sunday to 7 for Saturday. weekday runs from 0 for Monday to 6 for Sunday. So a filter like dayofweek(dt) == 1 keeps Sundays, not Mondays, and writing dayofweek(dt) >= 6 to mean "weekend" gets Friday and Saturday instead of Saturday and Sunday. Databricks SQL documents dayofweek with an example: dayofweek('2009-07-30') returns 5, a Thursday.
Checkpoint 2 of 5· Fill the gap
Which function completes this sample to return the day of week numbered 1 (Sunday) through 7 (Saturday)?
df = spark.createDataFrame([('2015-04-08',), ('2024-10-31',)], ['dt'])
df.select("*", dbf.typeof('dt'), dbf. ? ('dt')).show()dayofweek uses 1 = Sunday through 7 = Saturday. weekday uses 0 = Monday, dayofmonth returns the day of the month, and dayname returns text.
Source: docs.databricks.comdayofweek counts Sunday as 1, so Thursday is 5. weekday counts Monday as 0, so Thursday is 3. The Databricks SQL weekday page shows weekday(DATE'2009-07-30') returning 3 for that Thursday.
3.extract and date_part: pick the field at runtime
extract(field, source) and date_part(field, source) are the general-purpose extractors. Here field is a Column, usually a lit(...) naming the part, and source is a date, timestamp or interval column. The two take the same field names. Each named extractor is a shortcut for one of these calls: Databricks SQL describes year as a synonym for extract(YEAR FROM expr).
import datetime
from pyspark.sql import functions as dbf
df = spark.createDataFrame([(datetime.datetime(2015, 4, 8, 13, 8, 15),)], ['ts'])
df.select(
'*',
dbf.extract(dbf.lit('YEAR'), 'ts').alias('year'),
dbf.extract(dbf.lit('month'), 'ts').alias('month'),
dbf.extract(dbf.lit('WEEK'), 'ts').alias('week'),
dbf.extract(dbf.lit('D'), df.ts).alias('day'),
dbf.extract(dbf.lit('M'), df.ts).alias('minute'),
dbf.extract(dbf.lit('S'), df.ts).alias('second')
).show()Field names aren't case-sensitive here: both 'YEAR' and 'month' work. The single-letter codes are where people slip. In the documented example, 'M' is aliased as minute and 'D' as day. In a datetime format pattern, though, uppercase M means month and uppercase D means day-of-year. When you use extract or date_part, spell out the field name in full and you avoid the confusion.
Checkpoint 3 of 5· Check yourself
Following the documented extract example, what does extract(lit('M'), ts) return for the timestamp 2015-04-08 13:08:15?
In the documented example the 'M' field is aliased as minute, unlike the datetime-pattern letter M, which means month.
“dbf.extract(dbf.lit('M'), df.ts).alias('minute')”Source: docs.databricks.com
Checkpoint 4 of 5· Exam question
A CSV column `raw_date` (string) stores day-first dates such as `"25/03/2024"` (`dd/MM/yyyy`). A developer runs: ```python df.withColumn("parsed_date", to_date(col("raw_date"), "MM/dd/yyyy")) ``` and finds `parsed_date` is `null` for most rows, including `"25/03/2024"`. What is the correct fix, and why?
Correct answer: A — Change the format string to `dd/MM/yyyy` to match the day-first layout of `raw_date`; `to_date()` returns null whenever the pattern does not match the string's actual layout.
- A. `raw_date` stores day-first values, so the pattern must place `dd` before `MM`; a mismatched pattern order is the standard reason `to_date()` fails to parse otherwise valid strings and returns null.
- B. `to_date()` is designed to accept a `StringType` column as its normal use case and parses it according to the pattern; the failure here comes from a wrong pattern, not from receiving a string input.
- C. `to_date()` works correctly on pure date-only strings without any time component; the presence or absence of a time portion is not the cause of the null values in this scenario.
- D. `unix_timestamp()` still requires an explicit format argument that matches the string layout; it does not auto-detect arbitrary date layouts, so swapping functions without fixing the pattern would not resolve the issue.
- E. The legacy time parser policy changes which parsing engine is used for certain ambiguous patterns, but it does not make a `MM/dd/yyyy` pattern correctly interpret day-first strings like `"25/03/2024"`.
4.Why the session time zone affects extracted values
A DATE has no time zone, so its year, month and day are fixed. A TIMESTAMP (TIMESTAMP_LTZ) is different: it is a moment in time, and which calendar day or hour that moment lands on depends on where you view it from. Databricks applies the session time zone when it extracts fields from a TIMESTAMP. from_unixtime likewise formats the moment in the current time zone. Results from a pipeline that converts epoch seconds and then extracts the hour or day can therefore change with spark.sql.session.timeZone.
Checkpoint 5 of 5· Check yourself
The same TIMESTAMP_LTZ value is run through hour() in two sessions with different spark.sql.session.timeZone settings. What should you expect?
Fields extracted from a TIMESTAMP (TIMESTAMP_LTZ) are computed in the session time zone, so different sessions can disagree.
“When extracting fields from a TIMESTAMP (TIMESTAMP_LTZ), the result is based on the session timezone.”Source: docs.databricks.com
Exam traps
Each one states something that sounds right. Open it to see what is actually true.
1.dayofweek returns 1 for Monday.Why is that wrong?
dayofweek returns 1 for Sunday through 7 for Saturday. It's weekday that starts at 0 for Monday.
Covered in dayofweek and weekday count differently
2.In extract or date_part, the field code 'M' means month, as it does in a datetime pattern.Why is that wrong?
In the documented extract example, 'M' is aliased as minute. Spell out 'MONTH' or 'month' when you mean month.
Practise it for real
Turn a column of epoch seconds into a DATE and break it into year and day-of-week integers
1.Create spark.createDataFrame([(1428476400,)], ['unix_time']) and select dbf.from_unixtime('unix_time').
Why: from_unixtime is the epoch-to-string step, using the default yyyy-MM-dd HH:mm:ss pattern.
You should see: A string column showing the moment in your session time zone.
2.Wrap that expression in dbf.to_date(...) and include dbf.typeof(...) of the result.
Why: to_date turns the string into a typed DATE, which is what date logic needs.
You should see: typeof reports a date type, not a string.
3.Apply dbf.year, dbf.month and dbf.dayofweek to the date column.
Why: Specialized extractors return integers directly.
You should see: Three integer columns, with dayofweek between 1 (Sunday) and 7 (Saturday).
4.Set spark.conf.set("spark.sql.session.timeZone", "America/Los_Angeles"), rerun the from_unixtime select, then spark.conf.unset("spark.sql.session.timeZone").
Why: from_unixtime renders the moment in the current session time zone.
You should see: The string can differ from the first run if your default zone differs from Los Angeles.
Stuck? Get a nudge
If a column suddenly turns all null after you change a format string, check the pattern letters. Parsing functions such as unix_timestamp return null on failure instead of raising an error.
Sources
Every claim above is drawn from one of these pages, quoted as it was written on the date shown.
- 1.
“Extract the year of a given date/timestamp as integer.”
↩︎ One function per date component“pyspark.sql.Column: year part of the date/timestamp as integer.”
↩︎ Prediction - 2.
“dayname(col) | Returns the three-letter abbreviated day name from the given date.”
↩︎ One function per date component“weekday(col) | Returns the day of the week for date/timestamp (0 = Monday, 1 = Tuesday, ..., 6 = Sunday).”
↩︎ dayofweek and weekday count differently - 3.
“Whenever possible, use specialized functions like year.”
↩︎ One function per date component - 4.
“Ranges from 1 for a Sunday through to 7 for a Saturday”
↩︎ dayofweek and weekday count differently“Ranges from 1 for a Sunday through to 7 for a Saturday”
↩︎ Exam trap 1 - 5.
“An INTEGER where 0 = Monday and 6 = Sunday.”
↩︎ dayofweek and weekday count differently - 6.
“An INTEGER where 1 = Sunday, and 7 = Saturday.”
↩︎ dayofweek and weekday count differently - 7.
“supported string values are as same as the fields of the equivalent function extract.”
↩︎ extract and date_part: pick the field at runtime - 8.
“Extracts a part of the date/timestamp or interval source.”
↩︎ extract and date_part: pick the field at runtime“dbf.extract(dbf.lit('M'), df.ts).alias('minute')”
↩︎ Exam trap 2“dbf.extract(dbf.lit('M'), df.ts).alias('minute')”
↩︎ Checkpoint - 9.
“This function is a synonym for extract(YEAR FROM expr).”
↩︎ extract and date_part: pick the field at runtime“When extracting fields from a TIMESTAMP (TIMESTAMP_LTZ), the result is based on the session timezone.”
↩︎ Why the session time zone affects extracted values - 10.
“a string representing the timestamp of that moment in the current system time zone in the given format”
↩︎ Why the session time zone affects extracted values