22

I want to convert a value from bigint to datetime.

For example, I'm reading the HISTORY table of teamcity server. On the field build_start_time_server, I have this value on one record 1283174502729.

How can I convert it to a datetime value?

Pang
  • 9,564
  • 146
  • 81
  • 122
Chris
  • 253
  • 1
  • 2
  • 7
  • 1
    I changed the tags to hopefully make them more specific. Note that if you use "sql server" in the tag field this will go under the *sql* tag as well as the *server* tag but not under the *sql-server* tag which is probably what you want. – krock Sep 06 '10 at 09:31
  • 1
    What datetime value do you want for 1283174502729? – gbn Sep 06 '10 at 09:42
  • Agreed with @krock. Since your question isn't specific to what implementation of SQL you want (example: sql-server), there are a lot of answers here which are not applicable depending on the flavor of SQL. – hlin117 Jan 23 '19 at 21:30

9 Answers9

38

Does this work for you? It returns 30-8-2010 13:21:42 at the moment on SQL Server 2005:

select dateadd(s, convert(bigint, 1283174502729) / 1000, convert(datetime, '1-1-1970 00:00:00'))

I've divided by 1000 because the dateadd function won't work with a number that large. So you do lose a little precision, but it is much simpler to use.

Rob
  • 1,796
  • 14
  • 15
7

Slightly different approach:

Your scenario:

SELECT dateadd(ms, 1283174502729 / 86400000, (1283174502729 / 86400000) + 25567)
FROM yourtable

Generic code:

SELECT dateadd(ms, yourfield / 86400000, (yourfield / 86400000) + 25567)
FROM yourtable

Output:

August, 30 2010 00:00:14

SQL Fiddle: http://sqlfiddle.com/#!3/c9eb5a/2/0

Matt
  • 14,906
  • 27
  • 99
  • 149
4
CAST(SWITCHOFFSET(CAST(dateadd(s, convert(bigint, [t_stamp]) / 1000, convert(datetime, '1-1-1970 00:00:00')) AS DATETIMEOFFSET), DATENAME (TZoffset, SYSDATETIMEOFFSET())) AS DATETIME)
Michele La Ferla
  • 6,775
  • 11
  • 53
  • 79
stinkyjak
  • 304
  • 2
  • 8
  • I still don't understand the vote down. Are there errors? This works for 2008R2. Is it inefficient? How can I improve if I don't know what to improve. – stinkyjak Aug 29 '16 at 20:36
  • 1
    I would imagine the downvote was for providing a code only answer without any explanation as to what it is doing or why it works. – iamdave Oct 23 '17 at 07:59
1

The following takes new SQL terminology into account and will return the milliseconds (can also be modified for use in a calculated field.) [SQL Server 2012 or later]

declare @StartDate datetime2(3) = '1970-01-01 00:00:00.000'
, @milliseconds bigint = 1283174502729
, @MillisecondsPerDay int = 60 * 60 * 24 * 1000 -- = 86400000

SELECT  DATEADD(MILLISECOND, TRY_CAST(@milliseconds % @millisecondsPerDay AS 
INT), DATEADD(DAY, TRY_CAST(@milliseconds / @millisecondsPerDay AS INT), 
@StartDate));
Arthur Teter
  • 11
  • 2
1

To convert bigint to datetime/unixtime, you must divide these values by 1000000 (10e6) before casting to a timestamp.

SELECT
     CAST( bigIntTime_column / 1000000 AS timestamp) example_date
FROM example_table

Simple and easy solution which won't require any added library or function to be imported

whitetiger1399
  • 423
  • 3
  • 6
0

select Cast(Cast(19980324 as nvarchar) as Datetime)

Ajit Medhekar
  • 1,018
  • 1
  • 10
  • 39
0

If you want precision in milliseconds to be maintained then you could do as follows. Works on SQL server 2016

SELECT dateadd(ms, ((CONVERT(bigint, build_start_time_server)%1000)), 
       dateadd(ss, ((CONVERT(bigint, build_start_time_server)/1000)%60), 
       dateadd(mi, ((CONVERT(bigint, build_start_time_server)/1000)/60), '1970-01-01'))) FROM yourtable

The answer I got was

Monday, August 30, 2010 1:21 PM
Harikrishnan mk
  • 1
-2
DATEADD(second,YourValue, CAST('1970-01-01 00:00:00' AS datetime))
Owen Blacker
  • 4,117
  • 2
  • 33
  • 70
user6710275
  • 23
  • Downvoted. "Arithmetic overflow error converting expression to data type int." with the data provided in the question. – UnhandledExcepSean Aug 12 '16 at 17:02
  • 1
    Nevertheless with a bit of tweaks it could work, perhaps the poster can work it out a bit ? (upvoted to encourage him a bit :) ) – Gar Aug 12 '16 at 17:08
-3

Did you try FROM_UNIXTIME?

select from_unixtime('your_field') from 'your_table'

https://dev.mysql.com/doc/refman/5.7/en/date-and-time-functions.html#function_from-unixtime

Works for me.

Blue
  • 22,608
  • 7
  • 62
  • 92
tarcisiorosa
  • 7
  • 2