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.

MongoDB and CAP

Just read this great post, Call me maybe: MongoDB and the wrap-up post, Call me maybe: final thoughts. They're part of a series called Jepsen on behavior of various NoSQL databases in failure scenarios.

Very geeky but very interesting.

Completed MongoDB M102

As I mentioned earlier, I signed up for "M102: MongoDB for DBAs". Great class.

I just completed it yesterday and got 100% on everything, homework and final.

If you're interested in learning about MongoDB, this is a great way to get a broad exposure in a limited amount of time. Because it includes weekly lectures and homework, you can't procrastinate like you can with that technical book on your desk that you planned to read through.

Speaking at Data Modeling Zone 2014

My boss, Ryan Smith, and I will co-present on modeling in NoSQL at Data Modeling Zone 2014 here in Portland.

It's not until October of next year, so we don't have things nailed down yet, but it will be fun to work with Ryan on this.