Showing Seconds in Excel Date/Time Columns

This is a very simple solution, but clearly it has been a long time since I posted.

I was unable to post during the tenure at Nike. They are very obsessive about their brand, and as an employee, I was not allowed to post on my own blog.

As of today, I have been at Dish Network for 18 months. My new role is as a Senior Data Architect in the Big Data team. I will be supporting both the Big Data and Media Sales teams.

In this role, I am finally back to working directly with databases, this time Hadoop. Hadoop is completely new to me, so it's fun to learn.

Solution

Now to the solution, which is related to Excel, not Hadoop directly.

My Hadoop (Hive) query is pulling date/time columns with this:

SELECT ... from_unixtime(start_epoch) ...

It returns date/time values like this: "2018-12-14 17:05:15". But when I cut-and-paste that output into Excel, it drops the seconds from date/time values.

Here's how to show the seconds values in Excel.

1. Select the data/time column
2. Click the drop-down above "Number" in the ribbon (it defaults to "General")
3. Select "More Number Formats..."
4. Select "Custom"
5. Under "Type:", enter "yyyy-mm-dd hh:mm:ss"
6. Click OK

The date/time values now show like this: “2018-12-14 17:05:15”, exactly matching the output in Hive.