I have a database that has stored date/time data in a format that I am
not familiar with and I need a formula to translate the data I have
into a proper date/time.  For example, the database stores the
date/time of 2005/08/22 10:55 as 1124733317.  My question is: what is
the formula to calculate the date from the data that is stored in the
database to a normal date format?

---

Request for Question Clarification by justaskscott-ga on 25 Aug 2005 17:31 PDT

Could you provide any other examples of the format in which the
date/time is stored?  For example, do you have a time from earlier in
the morning on August 22 and the afternoon or evening of August 22,
and times from a different date?  Such examples might be the clue we
need to answer your question.

---

Request for Question Clarification by justaskscott-ga on 25 Aug 2005 18:01 PDT

Please ignore my initial request.  I've figured out the formula, and
will start work on an answer.

Hello sleenet,

The formula indicates not only how the date and time is calculated,
but that your database stored the time at 10:55:17 for a time zone
seven hours before Coordinated Universal Time (UTC) (which
corresponds, for example, to the Pacific Time Zone of the United
States during daylight saving time).

The formula is: treat the figure as a number of seconds to be added to
January 1, 1970 at 00:00:00 UTC.  1124733317 is 13017 days, 17 hours,
55 minutes, and 17 seconds after January 1, 1970 at 00:00:00 UTC, or
in other words, August 22, 2005 at 17:55:17 UTC.

The figure represents a Unix time.

"Unix time"
Wikipedia
http://en.wikipedia.org/wiki/Unix_time

A handy tool to convert Unix time to calendar time and back again is:

"Date/Epoch Converter (JavaScript/PerlScript)"
E.S.Q. Software
http://esqsoft.com/javascript_examples/date-to-epoch.htm

If you input 1124733317 in "Convert Epoch to Date," you'll see "Mon
Aug 22 13:55:17 EDT 2005" in the gray bar above the "New Date" line.

Before I found this converter, I undertook the following calculations
to determine the formula.  (You can skip over this material if you're
not interested in the background to the formula.)

1124733317 seconds divided by 86400 seconds per day equals a fraction
more than 13017 days (specifically, 13017 days and 64517 seconds).

August 22, 2005 at 10:55 UTC occurred after Julian date 2453604.5
(specifically, Julian date 2453604.95486, as calculated with the U.S.
Naval Observatory's Julian date converter at Julian Date Converter at
http://aa.usno.navy.mil/data/docs/JulianDate.html).

Julian date 2453604.5 is August 22, 2005 at 00:00:00 UTC.

13017 days before Julian date 2453604.5 is Julian date 2440587.5.

Julian date 2440587.5 is January 1, 1970 at 00:00:00 UTC.  (Note:
According to the article on Unix time cited above, a different form of
UTC existed prior to 1972.  But it appears that this distinction may
be safely ignored for present purposes.)

With January 1, 1970 at 00:00:00 UTC as the starting date (i.e., 0
Unix time), we count forward 1124733317 seconds.

As noted earlier, 1124733317 seconds equals 13017 days and 64517 seconds.

Thus, 1124733317 represents 64517 seconds after August 22, 2005 at 00:00:00 UTC.

64517 seconds, at 3600 seconds per hour, is 17 hours plus 3317 seconds.

3317 seconds, at 60 seconds per minute, is 55 minutes and 17 seconds.

Therefore, 1124733317 is 17 hours, 55 minutes, and 17 seconds after
August 22, 2005 at 00:00:00 UTC, or August 22, 2005 at 17:55:17 UTC.

- justaskscott


Search strategy --

Searched on various search engines for:

1124733317

[This number appeared on a few search results on MSN; one of the
results suggested to me that the figures covered a span of 86400
seconds in a day.]

Searched on Google for:

"31 dec 1969" site:answers.google.com [a search for a previous comment
referring to Unix time]
"january 1 1970"
unix
julian date
time zones
utc
"utc-7"