1. I don’t think you’re going to gain much by storing all of your datetimes as longs. Every language I can think of has ways to convert dates to other formats (string, long) use the preferred format for your database.

2. Store the dates in UTC. I think this makes sense for nearly every application, since many languages and APIs have a way to get Now() in UTC. Also, for most devices and browsers it’s trivial to figure out the timezone offset. Then it’s simple addition for displaying a user’s local time. You will save yourself much headache down the road.

There are a lot of intricacies in converting between time zones, especially when you consider daylight saving time and the fistful that aren’t even increments of one hour. If you’re using a database that has robust TIMESTAMP support, switching to integer storage would lose you access to the wealth of available operations. That may seem like the expedient thing to do, but having to re-implement all of the conversions in your own code is redundant and error-prone. Why reinvent an already well-proven wheel?

SQL92 defines an AT TIME ZONE clause that lets you recast a time in one time zone to another (PostgreSQL example):

-- Grab a value in one time zone and convert it to another
SELECT TIMESTAMP WITH TIME ZONE '2013-01-03 12:34:56 EST' AT TIME ZONE 'MST';

-- Store it as the user provided it, in his preferred time zone
INSERT into bar (foo) VALUES ('2013-01-03 12:34:56 EST');

-- Retrieve it in its original form
SELECT foo FROM bar WHERE ... ;

-- Retrieve it in the time zone some other user prefers
SELECT foo AT TIMEZONE 'PST' FROM bar WHERE ... ;

Several databases, Oracle and PostgreSQL among them, implement this.

I am also curious to hear some explanations why the, IMO
overcomplicated time zoned datetime data type has ever been used
across databases and not a uniform single time zone with conversions
just for display.

There’s nothing complicated about it; the zoned types just store more information. Sometimes it’s important to know the time zone in which an event occurred, and converting to a single time zone irrevocably obliterates that information. If you’re doing a marketing promotion and want to know which customers placed orders around lunchtime, an order that appears to have been placed at noon GMT isn’t very helpful when it was actually placed over breakfast in New York. To get all of that sorted out, you’re going to have to reinvent the aforementioned wheels, which your database may have already done for you.

This depends greatly on the purpose of your datetime information.

For a calendaring app, the ability to track time zones is essential. For a logging daemon, having all records in the same TZ is quite convenient. What kind is your app?

OTOH I don’t see what will be your gain from using a long int to store the timestamp. This looks like a premature optimization to me; you can store datetimes as GMT as well, and have your servers’ clocks always set to GMT (as many of us do). If this is the point of your question, then yes, having everything internally stored in the same TZ may be a good idea.

Is that GMT going to always be the same without any adjustments for Daylight Savings Time scenarios? I could imagine if suddenly a bunch of date data has been shifted because of DST that could be an issue and so you may have to contemplate what business rules would you have for this.

Jon Skeet’s “Epic Fail” has a section about Time Zones that I find interesting and quite funny at the same time.