display() does not show microseconds correctly

Use show() to display timestamp values with microsecond precision. display() is limited to millisecond precision.

Last published at: May 16th, 2022

Problem

You want to display a timestamp value with microsecond precision, but when you use display() it does not show the value past milliseconds.

For example, this Apache Spark SQL display() command:

%sql

display(spark.sql("select cast('2021-08-10T09:08:56.740436' as timestamp) as test"))

Returns a truncated value:

2021-08-10T09:08:56.740+0000

The DataFrame is converted to HTML internally before the output is rendered.

This limits the displayed results to millisecond precision.

It does not affect the stored value.

You should use show() instead of using display().

For example, this Apache Spark SQL show() command:

%sql

spark.sql("select cast('2021-08-10T09:08:56.740436' as timestamp) as test").show(truncate=False)

Returns the correct value:

2021-08-10 09:08:56.740436

As an alternative, you can create a second column and copy the value to the column as a string.

After conversion to a string, display() shows the full value.