View Question
 Question
 Subject: Date & Time Calculation Category: Science > Math Asked by: sleenet-ga List Price: \$50.00 Posted: 25 Aug 2005 14:35 PDT Expires: 24 Sep 2005 14:35 PDT Question ID: 560486
 ```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"```