What you will be able to do
- Convert a column of Unix epoch seconds into a formatted timestamp string with from_unixtime
- Turn a date or timestamp string back into epoch seconds with unix_timestamp, and predict when it returns null
- Produce a true DateType column with to_date, and render any date as a custom string with date_format
- Read and write datetime patterns, telling MM from mm and d from D
Key concept
Unix epoch seconds — A Unix time is a count of seconds since 1970-01-01 00:00:00 UTC. Spark's epoch functions convert between that number, a formatted string, and the DATE or TIMESTAMP types, and the type each one returns is what decides your next step.
1.Three representations of the same moment
Event data often arrives with time stored as an integer, such as 1428476400. That integer is a Unix time: seconds counted from 1970-01-01 00:00:00 UTC. People don't read it easily, and you can't group it by calendar day without converting it first. In Spark a single moment can be held three ways: as a number of epoch seconds (a long), as a string like 2015-04-08 12:12:12, or as a typed value, either DATE or TIMESTAMP.
The DATE type holds only a calendar day. It stores year, month and day and has no time zone, and the supported range runs from June 23 -5877641 CE to July 11 +5881580 CE. In SQL you write a DATE literal as DATE'2020-12-31'. If the literal isn't a valid date, Databricks raises an error.
Checkpoint 1 of 7· Check yourself
Which fields does a Spark DATE value hold?
A DATE holds only a calendar day and has no time zone. Epoch seconds and time of day belong to other representations.
“Represents values comprising values of fields year, month, and day, without a time-zone.”Source: docs.databricks.com
Most mistakes on this objective come from getting the return type wrong. The table below lists the conversion functions in the PySpark functions reference that move between epoch numbers, strings and typed values.
| Function | Input | Output |
|---|---|---|
| from_unixtime(timestamp[, format]) | Seconds since the Unix epoch | Formatted string |
| unix_timestamp([timestamp, format]) | Time string with a pattern | Unix time in seconds |
| timestamp_seconds(col) | Seconds since the Unix epoch | Timestamp |
| date_from_unix_date(days) | Days since 1970-01-01 | Date |
| unix_date(col) | Date | Days since 1970-01-01 |
| to_date(col[, format]) | Column such as a string | DateType |
| date_format(date, format) | Date, timestamp or string | String in the given format |
2.from_unixtime: epoch seconds to a timestamp string
from_unixtime(timestamp, format) takes a column of Unix time values and returns a string. The format argument is an optional literal string that defaults to yyyy-MM-dd HH:mm:ss. Pass a different pattern, such as yyyy-MM-dd or dd.MM.yyyy, to get a different layout.
The string shows the moment in the session's time zone, not in UTC. The documented example therefore sets spark.sql.session.timeZone to America/Los_Angeles before it runs and unsets it afterwards. Run the same epoch value under two session time zones and you can get two different strings.
from pyspark.sql import functions as dbf
df = spark.createDataFrame([(1428476400,)], ['unix_time'])
df.select('*', dbf.from_unixtime('unix_time')).show()Checkpoint 2 of 7· Fill the gap
Which function completes this sample so that the epoch-seconds column becomes a formatted timestamp string?
from pyspark.sql import functions as dbf
df = spark.createDataFrame([(1428476400,)], ['unix_time'])
df.select('*', dbf. ? ('unix_time')).show()from_unixtime goes from seconds since the epoch to a string. unix_timestamp goes the other way, from a string to seconds.
Source: docs.databricks.comCheckpoint 3 of 7· Exam question
A pipeline stores event times as Unix epoch seconds in a `LongType` column named `event_epoch`. An analyst needs a new column `event_date` holding the date as a string in `yyyy-MM-dd` format. Which code correctly creates it? ```python df.withColumn("event_date", ___) ```
Correct answer: A — from_unixtime(col("event_epoch"), "yyyy-MM-dd")
- A. `from_unixtime()` is built specifically to convert a numeric count of seconds since the Unix epoch into a formatted date/time string, so it correctly turns the epoch column into a `yyyy-MM-dd` string.
- B. `to_date()` parses a string column according to the supplied pattern; it does not interpret a numeric long value as seconds since the epoch, so applying it to a raw epoch column returns null for every row.
- C. `unix_timestamp()` converts a formatted date/time string into epoch seconds, which is the reverse of the conversion needed here, and it does not accept a numeric epoch column as meaningful input.
- D. `date_format()` expects a `DateType` or `TimestampType` column to reformat; called on a plain `LongType` epoch column it cannot interpret the value and yields null or an error rather than a valid date string.
- E. `to_timestamp()` parses a string according to the given pattern rather than treating a numeric input as seconds since the epoch, so the raw epoch long is not converted correctly by this call.
Sources3
3.unix_timestamp: strings back to epoch seconds
unix_timestamp(timestamp, format) does the reverse conversion. It parses a time string with the given pattern, yyyy-MM-dd HH:mm:ss by default, and returns Unix time in seconds as a long integer. Parsing uses the default time zone and locale. A string that doesn't parse produces null, not an error, so a wrong pattern can turn a whole column into nulls without any warning. With no argument, unix_timestamp() returns the current timestamp.
The documentation gives two examples. Under the default pattern, the string 2015-04-08 12:12:12 parses to 1428520332. With the pattern yyyy-MM-dd, the date-only string 2015-04-08 parses to 1428476400, the same number used in the from_unixtime example.
import pyspark.sql.functions as sf
df = spark.createDataFrame([('2015-04-08',)], ['dt'])
df.select('*', sf.unix_timestamp('dt', 'yyyy-MM-dd')).show()Checkpoint 4 of 7· Check yourself
A column holds strings like '08/04/2015'. You call unix_timestamp on it without a format argument. What happens?
unix_timestamp parses with the default pattern unless you pass one. When parsing fails it returns null instead of raising an error.
“using the default timezone and the default locale, returns null if failed.”Source: docs.databricks.com
Sources4
4.to_date for a real DATE, date_format for display
Two functions finish an epoch-to-date conversion. Which one you need depends on whether the result should be a typed value or text.
to_date(col, format) returns a column of DateType. If you omit the format, it follows the normal casting rules and is equivalent to col.cast("date"). If you supply a format, it parses with that pattern. Both calls in the example below turn the timestamp string 1997-02-28 10:30:00 into a date. A string from from_unixtime can be passed to to_date in the same way to get a DATE column.
date_format(date, format) goes the other direction. It accepts a date, timestamp or string and returns a string in the pattern you give, for example MM/dd/yyyy or dd.MM.yyyy, which produces strings like 18.03.1993. Use it to produce output text, such as a report label or a file-name fragment.
from pyspark.sql import functions as dbf
df = spark.createDataFrame([('1997-02-28 10:30:00',)], ['ts'])
df.select('*', dbf.to_date(df.ts)).show()
df.select('*', dbf.to_date('ts', 'yyyy-MM-dd HH:mm:ss')).show()from pyspark.sql import functions as dbf
df = spark.createDataFrame([('2015-04-08',), ('2024-10-31',)], ['dt'])
df.select("*", dbf.typeof('dt'), dbf.date_format('dt', 'MM/dd/yyyy')).show()Checkpoint 5 of 7· Match them up
Match each function to the type it returns
Tap a term, then the definition that fits it.
Only to_date returns a typed DATE. from_unixtime and date_format both return strings, and unix_timestamp returns seconds as a long.
“pyspark.sql.Column: date value as pyspark.sql.types.DateType type.”Source: docs.databricks.com
5.Datetime pattern letters
unix_timestamp, date_format, from_unixtime and to_date all read the same pattern letters. Letter case matters. The table lists the letters that most often get mixed up.
| Symbol | Meaning | Example |
|---|---|---|
| M | month-of-year | 7; 07; Jul; July |
| m | minute-of-hour | 30 |
| d | day-of-month | 28 |
| D | day-of-year | 189 |
| H | hour-of-day (0-23) | 0 |
| h | clock-hour-of-am-pm (1-12) | 12 |
| E | day-of-week | Tue; Tuesday |
The number of times a letter repeats also changes the output. For text fields such as E, fewer than four letters give the short form (Mon), exactly four give the full form (Monday), and five or more fail. Month follows the number/text rule: M prints 1 through 9 without padding, MM zero-pads, MMM gives Jan and MMMM gives January. For years, yy prints the last two digits. When parsing, yy uses a base value of 2000, so the year it produces always falls between 2000 and 2099.
Checkpoint 6 of 7· Check yourself
A colleague writes date_format(col, 'yyyy-mm-dd') and the middle field shows values like 30 or 00 instead of the month. Why?
Pattern letters are case-sensitive. M is month-of-year and m is minute-of-hour.
“m | minute-of-hour | number(2) | 30”Source: docs.databricks.com
Checkpoint 7 of 7· Exam question
A raw log column `log_time` (string) stores values like `"2024-03-15 14:30:00"` in the format `yyyy-MM-dd HH:mm:ss`. A downstream system requires a `LongType` column `epoch_seconds` holding the number of seconds since the Unix epoch. Which code correctly creates it? ```python df.withColumn("epoch_seconds", ___) ```
Correct answer: A — unix_timestamp(col("log_time"), "yyyy-MM-dd HH:mm:ss")
- A. `unix_timestamp()` parses a formatted date/time string using the given pattern and returns the matching number of seconds since the Unix epoch, which is exactly the conversion `log_time` needs.
- B. `from_unixtime()` converts a numeric epoch value into a formatted string, the reverse of what is required here, and it does not accept a string column like `log_time` as its first argument.
- C. `to_date()` keeps only the calendar date and discards the time-of-day portion, and casting the resulting `DateType` to `long` yields the number of days since 1970-01-01 rather than seconds, so the time component and correct unit are both lost.
- D. The pattern `MM/dd/yyyy HH:mm:ss` does not match the actual `yyyy-MM-dd HH:mm:ss` layout of `log_time`, so the parse fails and the expression returns null instead of the correct epoch value.
- E. `date_format()` reformats a date/timestamp value into another string; it does not produce a numeric epoch value, so the result is a formatted string rather than the required `LongType` seconds count.
Sources7
Exam traps
Each one states something that sounds right. Open it to see what is actually true.
1.from_unixtime returns a DATE or TIMESTAMP column you can use directly in date arithmetic.Why is that wrong?
It returns a formatted string. Wrap it in to_date, or use timestamp_seconds, if you need a typed value.
Covered in from_unixtime: epoch seconds to a timestamp string
2.unix_timestamp throws an error when a string doesn't match the pattern.Why is that wrong?
A failed parse returns null, so a wrong pattern quietly turns the column into nulls.
3.In a datetime pattern, mm means month.Why is that wrong?
Lowercase m is minute-of-hour. Month-of-year is uppercase M or MM.
Covered in Datetime pattern letters
Sources
Every claim above is drawn from one of these pages, quoted as it was written on the date shown.
- 1.
“Represents values comprising values of fields year, month, and day, without a time-zone.”
↩︎ Three representations of the same moment - 2.
“timestamp_seconds(col) | Converts the number of seconds from the Unix epoch (1970-01-01T00:00:00Z) to a timestamp.”
↩︎ Three representations of the same moment“date_from_unix_date(days) | Create date from the number of days since 1970-01-01.”
↩︎ Three representations of the same moment - 3.
“a string representing the timestamp of that moment in the current system time zone in the given format”
↩︎ from_unixtime: epoch seconds to a timestamp string“format to use to convert to (default: yyyy-MM-dd HH:mm:ss)”
↩︎ from_unixtime: epoch seconds to a timestamp string“Converts the number of seconds from unix epoch (1970-01-01 00:00:00 UTC) to a string representing the timestamp of that moment”
↩︎ Key concept“pyspark.sql.Column: formatted timestamp as string.”
↩︎ Exam trap 1“pyspark.sql.Column: formatted timestamp as string.”
↩︎ Prediction - 4.
“Convert time string with given pattern ('yyyy-MM-dd HH:mm:ss', by default) to Unix time stamp (in seconds)”
↩︎ unix_timestamp: strings back to epoch seconds“If timestamp is None, then it returns current timestamp.”
↩︎ unix_timestamp: strings back to epoch seconds“pyspark.sql.Column: unix time as long integer.”
↩︎ unix_timestamp: strings back to epoch seconds“using the default timezone and the default locale, returns null if failed.”
↩︎ Exam trap 2“using the default timezone and the default locale, returns null if failed.”
↩︎ Checkpoint - 5.
“By default, it follows casting rules to pyspark.sql.types.DateType if the format is omitted. Equivalent to col.cast("date").”
↩︎ to_date for a real DATE, date_format for display“pyspark.sql.Column: date value as pyspark.sql.types.DateType type.”
↩︎ Checkpoint - 6.
“Converts a date/timestamp/string to a value of string in the format specified by the date format given by the second argument.”
↩︎ to_date for a real DATE, date_format for display - 7.
“Exactly 4 pattern letters will use the full text form”
↩︎ Datetime pattern letters“For parsing, this will parse using the base value of 2000, resulting in a year within the range 2000 to 2099 inclusive.”
↩︎ Datetime pattern letters“M/L | month-of-year | month | 7; 07; Jul; July”
↩︎ Exam trap 3“m | minute-of-hour | number(2) | 30”
↩︎ Checkpoint