98

I have a SQL-server timestamp that I need to convert into a representation of time in milliseconds since 1970. Can I do this with plain SQL? If not, I've extracted it into a DateTime variable in C#. Is it possible to get a millisec representation of this ?

Thanks,
Teja.

Tejaswi Yerukalapudi
  • 8,987
  • 12
  • 60
  • 101
  • 1
    For the latter question: `(now - Epoch).TotalMilliseconds`, where `now` and `Epoch` are DateTime objects. –  May 10 '11 at 20:14
  • .NET has evolved: DateTimeOffset.UtcNow.ToUnixTimeMilliseconds() – Timeless Apr 04 '23 at 13:25

7 Answers7

164

You're probably trying to convert to a UNIX-like timestamp, which are in UTC:

yourDateTime.ToUniversalTime().Subtract(
    new DateTime(1970, 1, 1, 0, 0, 0, DateTimeKind.Utc)
    ).TotalMilliseconds

This also avoids summertime issues, since UTC doesn't have those.

Andomar
  • 232,371
  • 49
  • 380
  • 404
  • 2
    this gives a fraction as output, for example: "1552678714362.79" Where is the ".79" coming from? – Sharif Yazdian Mar 15 '19 at 19:39
  • 3
    @SharifYazdian It's exactly what you would expect, it's 0.79 milliseconds. The system time is measured in ticks. Given that 1 millisecond has 10000 ticks, `DateTime` and `TimeSpan` store their values with higher precision than whole milliseconds. 0.79 ms = 7900 ticks. If you need a whole number, you can use `long ms = myTimeSpan.TotalTicks / 10000;`. – LWChris Apr 26 '19 at 01:11
  • 2
    In .NET Core (>2.1) you can use `DateTime.UnixEpoch` instead of declaring the date. – Thom May 27 '19 at 07:52
  • 1
    This code has a problem: it assumes the `DateTime` (look the note in the contract of [`ToUniversalTime`](https://learn.microsoft.com/en-us/dotnet/api/system.datetime.touniversaltime)) is in local time which it could be not. For example it's common to sample time with `DateTime.UtcNow` or it could be in another timezone. – ceztko Nov 20 '19 at 20:02
  • @ceztko For `DateTime.UtcNow` this wouldn't be an issue, since the UtcNow DateTime has its Kind property set to Utc the `ToUniversalTime` method doesn't convert anything. If your DateTime is representing another timezone than local or UTC though, there might be an issue (but you are better off using DateTimeOffset rather than DateTime in those kind of situations anyway) – Leon Lucardie Feb 15 '21 at 14:04
79

In C#, you can write

(long)(date - new DateTime(1970, 1, 1)).TotalMilliseconds
SLaks
  • 868,454
  • 176
  • 1,908
  • 1,964
51

As of .NET 4.6, you can use a DateTimeOffset object to get the unix milliseconds. It has a constructor which takes a DateTime object, so you can just pass in your object as demonstrated below.

DateTime yourDateTime;
long yourDateTimeMilliseconds = new DateTimeOffset(yourDateTime).ToUnixTimeMilliseconds();

As noted in other answers, make sure yourDateTime has the correct Kind specified, or use .ToUniversalTime() to convert it to UTC time first.

Here you can learn more about DateTimeOffset.

Bob
  • 686
  • 7
  • 7
  • 1
    ToUnixTimeMilliseconds() doesn't actually exist in .Net 3.5. – NickLokarno Oct 15 '17 at 20:34
  • 2
    According to [MSDN](https://msdn.microsoft.com/en-us/library/system.datetimeoffset.tounixtimemilliseconds(v=vs.110).aspx), `ToUnixTimeMilliseconds()` is available since .NET 4.6 – N. M. Feb 24 '18 at 14:17
  • 1
    @kayleeFrye_onDeck The OP is asking about a timestamp pulled from a database, so your comment about `.Now` doesn't directly apply to this question. – Bob Mar 06 '19 at 14:49
3

There are ToUnixTime() and ToUnixTimeMs() methods in DateTimeExtensions class

DateTime.UtcNow.ToUnixTimeMs()

Kate Orlova
  • 3,225
  • 5
  • 11
  • 35
dmirg
  • 130
  • 4
2

This other solution for covert datetime to unixtimestampmillis C#.

private static readonly DateTime UnixEpoch = new DateTime(1970, 1, 1, 0, 0, 0, DateTimeKind.Utc);

public static long GetCurrentUnixTimestampMillis()
{
    DateTime localDateTime, univDateTime;
    localDateTime = DateTime.Now;          
    univDateTime = localDateTime.ToUniversalTime();
    return (long)(univDateTime - UnixEpoch).TotalMilliseconds;
}
samsanthosh2008
  • 91
  • 1
  • 3
2
SELECT CAST(DATEDIFF(S, '1970-01-01', SYSDATETIME()) AS BIGINT) * 1000

This does not give you full precision, but DATEDIFF(MS... causes overflow. If seconds are good enough, this should do it.

1

Using the answer of Andoma, this is what I'm doing

You can create a Struct or a Class like this one

struct Date
    {
        public static double GetTime(DateTime dateTime)
        {
            return dateTime.ToUniversalTime().Subtract(new DateTime(1970, 1, 1, 0, 0, 0, DateTimeKind.Utc)).TotalMilliseconds;
        }

        public static DateTime DateTimeParse(double milliseconds)
        {
            return new DateTime(1970, 1, 1, 0, 0, 0, DateTimeKind.Utc).AddMilliseconds(milliseconds).ToLocalTime();
        }

    }

And you can use this in your code as following

DateTime dateTime = DateTime.Now;

double total = Date.GetTime(dateTime);

dateTime = Date.DateTimeParse(total);

I hope this help you

Pablo V
  • 71
  • 2