Active1 month ago
The C date and time functions are a group of functions in the standard library of the C programming language implementing date and time manipulation operations. They provide support for time acquisition, conversion between date formats, and formatted output to strings. The Unix epoch (or Unix time or POSIX time or Unix timestamp) is the number of seconds that have elapsed since January 1, 1970 (midnight UTC/GMT), not counting leap seconds (in ISO 8601: 1970-01-01T00:00:00Z). Literally speaking the epoch is Unix time 0 (midnight 1/1/1970), but 'epoch' is often used as a synonym for 'Unix time'.
How can I convert UNIX timestamp (bigint) to DateTime in SQL Server?
Salman A195k6969 gold badges357357 silver badges455455 bronze badges
salmansalman73122 gold badges88 silver badges1717 bronze badges
13 Answers
Diego14.6k55 gold badges4949 silver badges6161 bronze badges
KM.KM.86k2727 gold badges152152 silver badges195195 bronze badges
This worked for me:
In case any one wonders why 1970-01-01 ,This is called Epoch time.Below is a quote from wikipedia
the number of seconds that have elapsed since 00:00:00 Coordinated Universal Time (UTC), Thursday, 1 January 1970,[1][note 1] not counting leap seconds
TheGameiswar22.5k55 gold badges3434 silver badges6666 bronze badges
Daniel LittleDaniel Little12.5k99 gold badges5757 silver badges8484 bronze badges
Like this
add the Unix (epoch) datetime to the base date in seconds
this will get it for now (2010-05-25 07:56:23.000)
If you want to go reverse, take a look at this http://wiki.lessthandot.com/index.php/Epoch_Date
SQLMenaceSQLMenace116k2323 gold badges188188 silver badges216216 bronze badges
If anyone getting 'Arithmetic overflow error converting expression to data type int' due to unix timestamp is in bigint (instead of int), you can use this:
SELECT DATEADD(S, CONVERT(int,LEFT(1462924862735870900, 10)), '1970-01-01')FROM TABLE
Replace the hardcoded timestamp for your actual column with unix time stamp
Source: MSSQL bigint Unix Timestamp to Datetime with milliseconds
Community♦
cepixcepix
This will do it:
Instead of !precision! use: ss,ms or mcs according to the precision of the timestamp.Bigint is capable to hold microsecond precision.
Ovidiu PacurarOvidiu Pacurar6,48822 gold badges2525 silver badges3737 bronze badges
Test this:
Sql server:
MySql server:
Mohamad HamoudayMohamad Hamouday
This is building off the work Daniel Little did for this question, but taking into account daylight savings time (works for dates 01-01 1902 and greater due to int limit on dateadd function):
We first need to create a table that will store the date ranges for daylight savings time (source: History of time in the United States):
Now we create a function for each American timezone. This is assuming the unix time is in milliseconds. If it is in seconds, remove the /1000 from the code:
Pacific
Eastern
Central
Mountain
Hawaii
Arizona
Alaska
jymbojymbo92311 gold badge88 silver badges1818 bronze badges
Better? This function converts unixtime in milliseconds to datetime. It's lost milliseconds, but still very useful for filtering.
MtAtMtAt
Adding n seconds to
1970-01-01
will give you a UTC date because n, the Unix timestamp, is the number of seconds that have elapsed since 00:00:00 Coordinated Universal Time (UTC), Thursday, 1 January 1970.In SQL Server 2016, you can convert one time zone to another using
AT TIME ZONE
. You just need to know the name of the time zone in Windows standard format:Or simply:
Notes:
- You can chop off the timezone information by casting
DATETIMEOFFSET
toDATETIME
. - The conversion takes daylight savings time into account. Pacific time was UTC-08:00 on January 2018 and UTC-07:00 on Jun 2018.
195k6969 gold badges357357 silver badges455455 bronze badges
סטנלי גרונן1,83288 gold badges2727 silver badges4747 bronze badges
user3450075user3450075
I had to face this problem, too. Unfortunately, none of the answers (here and in dozens of other pages) has been satisfactory to me, as I still cannot reach dates beyond the year 2038 due to 32 bit integer casts somewhere.
A solution that did work for me in the end was to use
float
variables, so I could have at least a max date of 2262-04-11T23:47:16.854775849
. Still, this doesn't cover the entire datetime
domain, but it is sufficient for my needs and may help others encountering the same problem.There are some points to consider:
- 100ns precision is the requirement in my case, however this seems to be the standard resolution for 64 bit unix timestamps. If you use any other resolution, you have to adjust
@ticksofday
and the first line of the algorithm accordingly. - I'm using other systems that have their problems with time zones etc. and I found the best solution for me would be always using UTC. For your needs, this may differ.
1900-01-01
is the origin date fordatetime2
, just as is the epoch1970-01-01
for unix timestamps.float
s helped me to solve the year-2038-problem and integer overflows and such, but keep in mind that floating point numbers are not very performant and may slow down processing of a big amount of timestamps. Also, floats may lead to loss of precision due to roundoff errors, as you can see in the comparison of the example results for the max date above (here, the error is about 1.4425ms).- In the last line of the algorithm there is a cast to
datetime
. Unfortunately, there is no explicit cast from numeric values todatetime2
allowed, but it is allowed to cast numerics todatetime
explicitly and this, in turn, is cast implicitly todatetime2
. This may be correct, for now, but may change in future versions of SQL Server: Either there will be adateadd_big()
function or the explicit cast todatetime2
will be allowed or the explicit cast todatetime
will be disallowed, so this may either break or there may come an easier way some day.
![Convert unix time to date mathematics pdf Convert unix time to date mathematics pdf](/uploads/1/2/5/1/125108783/685467408.png)
61311 gold badge44 silver badges2020 bronze badges
If the time is in milliseconds and one need to preserve them:
gotqngotqn22.9k3232 gold badges125125 silver badges198198 bronze badges
Vitaly IlyuhinVitaly Ilyuhin
Not the answer you're looking for? Browse other questions tagged sqlsql-serverdatedatetimetimestamp or ask your own question.
Active5 months ago
How do I convert an epoch timestamp to a human readable format on the cli? I think there's a way to do it with date but the syntax eludes me (other ways welcome).
Gilles577k140140 gold badges11901190 silver badges17021702 bronze badges
xenoterracidexenoterracide27.4k5656 gold badges162162 silver badges227227 bronze badges
11 Answers
On *BSD:
On Linux (specifically, with GNU coreutils ≥5.3):
With older versions of GNU date, you can calculate the relative difference to the UTC epoch:
![Convert Unix Time To Date Mathematics Convert Unix Time To Date Mathematics](http://sstut.com/i/Padding-extra-zero-in-front-of-single-digit-date.png)
If you need portability, you're out of luck. The only time you can format with a POSIX shell command (without doing the calculation yourself) line is the current time. In practice, Perl is often available:
GillesGilles577k140140 gold badges11901190 silver badges17021702 bronze badges
date -d @1190000000
Replace 1190000000 with your epochfschmittfschmitt
This can come in handy for those applications which use epoch time in the logfiles:
Stefan LasiewskiStefan Lasiewski9,4102020 gold badges6262 silver badges8080 bronze badges
With
bash-4.2
or above:(where
%F %T
is the strftime()
-type format)That syntax is inspired from
ksh93
.In
ksh93
however, the argument is taken as a date expression where various and hardly documented formats are supported.For a Unix epoch time, the syntax in
ksh93
is:ksh93
however seems to use its own algorithm for the timezone and can get it wrong. For instance, in Britain, it was summer time all year in 1970, but:335k5858 gold badges654654 silver badges10321032 bronze badges
Custom format with GNU
date
:Or with GNU
awk
:Linked SO question: https://stackoverflow.com/questions/3249827/convert-from-unixtime-at-command-line
Community♦
Ivan ChauIvan Chau
al.al.
With
zsh
you could use the strftime
builtin:e.g.
There's also
dateconv
from dateutils
:keep in mind
don_crisstidon_crisstidateutils
tools default to UTC
(add -z your/timezone
if needed).55.1k1818 gold badges153153 silver badges180180 bronze badges
If your epoch time is in milliseconds instead of seconds, remove the last three digits before passing it to
date -d
:This gives incorrect data. Remove the last three digits.
KnockTurnAlKnockTurnAl
You could also use a little C program for printing the datetime in the format that can be directly parsed by shell
usage:
MeowMeow
Wouldn't be a real solution without a little node.js:
add that to
~/.bash_aliases
and make sure its sourced in ~/.bashrc
with . ~/.bash_aliases
To get node on your system goto http://nvm.sh and run the curl command. It'll install node version manager (nvm) which allows you to switch versions of node.
Just type
chovychovynvm ls-remote
and pick a version to nvm install <version>
.61933 gold badges77 silver badges1616 bronze badges
There is a easier way to do this:(([System.DateTimeOffset]::FromUnixTimeMilliSeconds($unixtime)).DateTime).ToString('s')
Kulwinder SinghKulwinder Singh