Convert Unix Time Stamp

Posted on 2004-08-18
Last Modified: 2008-02-01

I have a table in a SQL Server 2000 Database. One of the columns holds Unix Time Stamp values. Is there a way to convert the time stamp to a viewer friendly version?




08 / 17 / 2004

Thanks in advance...
Question by:prodier
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
LVL 17

Expert Comment

ID: 11832945
how are you getting that date from 1092737700?
LVL 37

Accepted Solution

gregoryyoung earned 500 total points
ID: 11833912
DATEADD(SECOND, columnname, '01/01/1970') AS expr1
LVL 37

Expert Comment

ID: 11833924
unix timestamps are seconds since standard epoch.
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

LVL 17

Expert Comment

ID: 11833954
yuck, lol
LVL 37

Expert Comment

ID: 11833991
if you wanted to do it in code the operation would be the same ...

to GET a unix timestamp from a Date ...

            static private int UnixTimeStamp(DateTime d) {
                  TimeSpan t = d.Subtract(DateTime.Parse("1/1/1970"));
                  return (int) Math.Floor(t.TotalSeconds) ;
LVL 20

Expert Comment

ID: 11840112

// calendar reference, at
const int NumDaysTo10000 = 3652059;
const int NumDaysTo1970 = 719162;

const long NumTicksPerDay = 864000000000;
const long NumTicksPerSecond = 10000000;

const long MaxTicks = NumDaysTo10000 * NumTicksPerDay -1;
const long MinTicks = 0;

// number of 100 nanosecond units from 1/1/1601 to 1/1/1970
Int64 TicksTo1970   = NumDaysTo1970 * NumTicksPerDay;

// number of seconds elapsed in Unix epoch timestamp
int elapsedEpoch = 1092737700;

// number of 100 nanosecond units from 1/1/1970 to now
Int64 TicksFrom1970 = elapsedEpoch * NumTicksPerSecond;

long universalTicks = TicksTo1970 + TicksFrom1970;
DateTime univDT = new DateTime(universalTicks);

// display elapsedEpoch in UTC

TimeZone tz = TimeZone.CurrentTimeZone;
long localTicks = universalTicks + tz.GetUtcOffset(univDT).Ticks;

if ((ulong)localTicks>(ulong)MaxTicks)
      if (localTicks>MaxTicks)
            localTicks = MaxTicks;
            localTicks = MinTicks;

// Display in local time
Console.WriteLine( new DateTime(localTicks) );

This site provides unix timestamp to readable timestamp



Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Article by: jpaulino
XML Literals are a great way to handle XML files and the community doesn’t use it as much as it should.  An XML Literal is like a String ( Literal, only instead of starting and ending with w…
I think the Typed DataTable and Typed DataSet are very good options when working with data, but I don't like auto-generated code. First, I create an Abstract Class for my DataTables Common Code.  This class Inherits from DataTable. Also, it can …
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
Finding and deleting duplicate (picture) files can be a time consuming task. My wife and I, our three kids and their families all share one dilemma: Managing our pictures. Between desktops, laptops, phones, tablets, and cameras; over the last decade…

696 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question