Unix Timestamp or Epoch Timestamp to Datetime in SQL Server

Today I was working on extracting files from Amazon S3 to Azure Blob storage and I need to to refer to a control file to determine the start and end time of my extract. But unfortunately the timestamp in the control file was in Unix format. 

Select lastSuccessfulWriteTimestamp from from ETL_CONTROL_TABLE

------------------------

1590796601336

------------------------

Now i need to convert this Unix Timestamp or Epoch to Datetime format so that my extract job can understand the the actual start date and end date.

Now before proceeding let me explain what's a Unix Timestamp or Epoch Timestamp?

Unix Timestamp or Epoch Timestamp is the number of  "seconds" from 1st January 1970 00:00:00 hrs.

So a datetime  where 

Date : 1st January 1970 

Time : 03 : 00 : 00 am 

will be represented in Unix Timestamp or Epoch Timestamp as  

3 (hours) * 60 (min) *  60 (sec) = 10,800  (this is timestamp in seconds)

3 (hours) * 60 (min) *  60 (sec)  * 1000 = 1,08,00,000 (this is timestamp in milliseconds)

So the Unix time I got is in milliseconds (Since my process is in real time I need to preserve the milliseconds precisions also)

So here is the SQL logic i used in SQL server to covert this Unix Timestamp or Epoch Timestamp to Datetime.

SELECT DATEADD(MILLISECOND, CAST(RIGHT(lastSuccessfulWriteTimestamp, 3) AS INT) - DATEDIFF(MILLISECOND,GETDATE(),GETUTCDATE()), DATEADD(SECOND, CAST(LEFT(lastSuccessfulWriteTimestamp, 10) AS INT), '1970-01-01T00:00:00')) as Converted_time from ETL_CONTROL_TABLE

Converted_time

----------------------

2020-05-29 23:56:41.337

----------------------

In case you dont require the millisecond precision we can use the below query.

SELECT DATEADD(S, CONVERT(int,LEFT(lastSuccessfulWriteTimestamp, 10)), '1970-01-01') as Converted_time from ETL_CONTROL_TABLE

Converted_time

----------------------------

2020-05-29 23:56:41.000

----------------------------

As you can see the millisecond values are populated as 000.

Hope this was useful. Thank you

Comments

Post a Comment

Popular posts from this blog

SCD 2 Using Merge statement in SQL server

Image
 In this example we will look at how to implement SCD2 using merge statement. Target table: Source Table: So if you compare the source and target table , you can see that  * New address for customerid = 1 in the source table , so "New" address needs to be inserted (true) and  "current" address in traget table (customer) needs to be updated as 'false'. * No changes to the address of customer 3 , so no changes. * A "new" customer with customerid = 4 , so that needs to be inserted. Query for SCD 2 : MERGE INTO customers USING (    -- These rows will either UPDATE the current1 addresses of existing customers or INSERT the new addresses of new ----customers   SELECT updates.customerId as mergeKey, updates.*   FROM updates      UNION ALL      -- These rows will INSERT new addresses of existing customers    -- Setting the mergeKey to NULL forces these rows to NOT MATCH and be INSERTed.   SELECT NULL as mergeKe...
Read more

Flatten / Parsing XML using Azure ADF (Azure data factory)

Image
                           Flatting/Parsing Hierarchical XML using ADF (Azure data factory) Sharing a method of flatting Hierarchical XML using an inbuild method in ADF  ( since its UI based I felt its easier than databricks option)   To flatten “Hierarchical XML” you can use “Flatten” transform in ADF Dataflow.   My source XML structure is mentioned below. To Flatten the structure , we can create a ADF dataflow similar to screen shot mentioned below and use ‘Flatten’ transformation. Steps involved : Step 1 : Create a dataflow in ADF Step 2 : Enable “Data flow debug” option. ·        This will create a cluster in the backend, for the dataflow to run. ·        Cost of this option is priced as 1(hour) x 8 (general purpose cores) x $0.274 = $2.19 ·        This is required only during the development phase ...
Read more

Integer division to float division in SQL server

Image
Problem statement : I have a use case where i need to see how much percentage each day with respect to days in a week. Solution in Mathematics :  since there are 7 days in a week , so the solution will be (1/7) * 100 = 14.28 Solution in SQL server: If we apply the same logic in SQL server the result will be 0 because with will be a integer vision. To convert this into float division we need to us 'cast (<integer> as float)' in the denominator. Please refer to the screen shot below.  
Read more