Milliseconds DateTime representation?

Question

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

Thank,
Thea

Answer 1

You are probably trying to convert a UNIX-like timestamp that is in UTC:

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

It also avoids daylight saving time since UTC does not have this data.

Answer 2

In C # you can write

 (long)(date - new DateTime(1970, 1, 1)).TotalMilliseconds 

Answer 3

Starting with .NET 4.0, you can use the DateTimeOffset object to get unix milliseconds. It has a constructor that accepts a DateTime object, so you can just pass your object as shown below.

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

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

Here you can learn more about DateTimeOffset .

Answer 4

 SELECT CAST(DATEDIFF(S, '1970-01-01', SYSDATETIME()) AS BIGINT) * 1000 

This does not give you complete accuracy, but DATEDIFF(MS... causes an overflow. If the seconds are good enough, this should do it.

Answer 5

This is another secret datetime solution for 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; } 

Answer 6

angular says about date parater:

Date for formatting as a Date object, milliseconds (string or number) or various ISO 8601 date and time string formats (e.g. yyyy-MM-ddTHH: mm: ss.sssZ and its shorter versions, such as yyyy-MM-ddTHH: mmZ, yyyy-MM-dd or yyyyMMddTHHmmssZ). If the time zone is missing from the string input, it is considered that the time is the local time zone.

Andomar's response is converted to datetime in full milliseconds.

And you should see the following links.

https://docs.angularjs.org/api/ng/filter/date

Given a DateTime object, how do I get an ISO 8601 date in string format?

Answer 7

Using Andoma's answer, this is what I do

You can create a Struct or class like this

 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

 DateTime dateTime = DateTime.Now; double total = Date.GetTime(dateTime); dateTime = Date.DateTimeParse(total); 

I hope this helps you

Answer 8

 Declare @MsPerDay bigint Set @MsPerDay = 86400000 Declare @StartDate datetime Set @StartDate = '19700101' Declare @TargetDate datetime Set @TargetDate = CURRENT_TIMESTAMP Select DateDiff(d,@StartDate,@TargetDate) * @MsPerDay + DateDiff(ms,DateAdd(d,DateDiff(d,0,@TargetDate),0),@TargetDate) 

In pseudo code:

The delta of days between 1970-01-01 and the target lifetime, multiplied by the number of milliseconds per day plus milliseconds from midnight of the target time and time until the target time and time.