There is a very easy method to convert Unix TimeStamp to DateTime. Let us learn that in today’s blog post.
Here is a very simple example of it.
DECLARE @UnixDate BIGINT = 1392349571299 SELECT CAST(DATEADD(ms, CAST(RIGHT(@UnixDate,3) AS SMALLINT), DATEADD(s, @UnixDate / 1000, '1970-01-01')) AS DATETIME2(3))
When you run the script above it will display the result as 2014-02-14 03:46:11.300
Yes, that’s it. It is that simple to convert Unix TimeStamp to Datetime.
Learn MySQL – Learning Path
- Course 1: MySQL Fundamental – 1
- Course 2: MySQL Fundamental – 2
- Course 3: MySQL Indexing for Performance
- Course 4: MySQL Query Optimization and Performance Tuning
- Course 5: MySQL Workbench Guided Tour
- Course 6: MySQL Backup and Recovery Fundamentals
- Course 7: Monitoring MySQL with Performance Schema
- Course 8: phpMyAdmin Fundamentals
Here are six-part blog post series I have written based on my last 10 years of experience helping with the Comprehensive Database Performance Health Check. I strongly recommend you to read them as they walk you through my business model.
- Consulting 101 – Why Do I Never Take Control of Computers Remotely?
- Consulting 102 – Why Do I Give 100% Guarantee of My Services?
- Consulting 103 – Why Do I Assure SQL Server Performance Optimization in 4 Hours?
- Consulting 104 – Why Do I Give All of the Performance-Tuning Scripts to My Customers?
- Consulting 105 – Why Don’t I Want My Customers to Return Because of the Same Problem?
- Consulting Wrap Up – What Next and How to Get Started
If you have any questions, you can always reach out to me on Twitter.
Reference: Pinal Dave (http://blog.SQLAuthority.com)
Simple, but wrong. The 299 miliseconds are converted to 300.
This is the correct conversion
SELECT DATEADD(SECOND, @UnixDate / 1000, DATETIME2FROMPARTS(1970, 1, 1, 0, 0, 0, @UnixDate % 1000, 3));
And to overcome the INT problem, use this
DECLARE @UnixDate BIGINT = 4592349571299;
SELECT DATEADD(MILLISECOND, @UnixDate % 86400000, DATEADD(DAY,@UnixDate / 86400000, CAST(‘19700101 00:00:00.000’ AS DATETIME2(3))));