Unix time stamp conversion in Azure synapse analytics

Question

Unix time stamp conversion in Azure synapse analytics

Rohit Kulkarni 731

Hello Team,

I am using the below script to do refining the data in silver layer:

For particular column CreatedDate the data is appearing in the Unix format.Please refer the below :

I need to convert the data into YYYY-MM-DD : HH:MM:SSSS).

Please advise how it can be done.

Regards
RK

Michael Taylor 61,116 Reputation points

2022-10-10T15:09:50.107+00:00
I know nothing about Spark but it ultimately looks like you have a SQL query in there so try using the standard SQL approach to handling this. In your select call don't return CreatedDate directly but wrap it in a call to dateadd. Unix timestamps are seconds since 1/1/1970 so you just need to add the values together in your select statement.

select DATEADD(S, CreatedDate, '1970-01-01')

The assumption here is that CreatedDate is UTC. If it isn't then the value may be off. Also note that DATEADD uses INT which is limited to 2 billion. Above that you have to break it up into 2 calls. Refer to this post with a solution for that.
Rohit Kulkarni 731 Reputation points

2022-10-11T03:48:57.32+00:00

CreateDate is a Bigint.datatype in the current scenario.
Rohit Kulkarni 731 Reputation points

2022-10-11T06:27:34.407+00:00

import com.microsoft.spark.sqlanalytics
from com.microsoft.spark.sqlanalytics.Constants import Constants
from pyspark.sql.functions import col

Read from existing internal table

dfAsset = (spark.read
.option(Constants.SERVER, "xyz.sql.azuresynapse.net")
.synapsesql("abc.Salesforce.Asset")
.select("Id","ContactId","AccountId","ParentId","RootAssetId",DATEADD(S,"CreatedDate",'1970-01-01'),"CreatedById")
.filter(col("productCode").contains("11061164"))
.limit(10))

Show contents of the dataframe

dfAsset.show()
I am getting error :

NameError: name 'DATEADD' is not defined
Michael Taylor 61,116 Reputation points

2022-10-11T14:50:29.253+00:00

The docs say it is supported here. But you're using the select function that is expecting string column names. I don't know enough about Synapse to figure out the syntax you need to use to call SQL-functions inside your select call. Maybe you just need to wrap it in double quotes (and fix the quotes inside of it) to send it to the DB unchanged. I'm not really sure.

Perhaps someone else can provide better direction. In any case you'll have to do 2 adds because DateAdd does not support BIGINT so you'll have to split it up. Perhaps a user defined function (which I believe Synapse also supports) would be a better solution here.

Your answer

Michael Taylor 61,116 Reputation points

2022-10-10T15:09:50.107+00:00

I know nothing about Spark but it ultimately looks like you have a SQL query in there so try using the standard SQL approach to handling this. In your select call don't return CreatedDate directly but wrap it in a call to dateadd. Unix timestamps are seconds since 1/1/1970 so you just need to add the values together in your select statement.

select DATEADD(S, CreatedDate, '1970-01-01')

The assumption here is that CreatedDate is UTC. If it isn't then the value may be off. Also note that DATEADD uses INT which is limited to 2 billion. Above that you have to break it up into 2 calls. Refer to this post with a solution for that.
Rohit Kulkarni 731 Reputation points

2022-10-11T03:48:57.32+00:00

CreateDate is a Bigint.datatype in the current scenario.
Rohit Kulkarni 731 Reputation points

2022-10-11T06:27:34.407+00:00

import com.microsoft.spark.sqlanalytics
from com.microsoft.spark.sqlanalytics.Constants import Constants
from pyspark.sql.functions import col

Read from existing internal table

dfAsset = (spark.read
.option(Constants.SERVER, "xyz.sql.azuresynapse.net")
.synapsesql("abc.Salesforce.Asset")
.select("Id","ContactId","AccountId","ParentId","RootAssetId",DATEADD(S,"CreatedDate",'1970-01-01'),"CreatedById")
.filter(col("productCode").contains("11061164"))
.limit(10))

Show contents of the dataframe

dfAsset.show()
I am getting error :

NameError: name 'DATEADD' is not defined
Michael Taylor 61,116 Reputation points

2022-10-11T14:50:29.253+00:00

The docs say it is supported here. But you're using the select function that is expecting string column names. I don't know enough about Synapse to figure out the syntax you need to use to call SQL-functions inside your select call. Maybe you just need to wrap it in double quotes (and fix the quotes inside of it) to send it to the DB unchanged. I'm not really sure.

Perhaps someone else can provide better direction. In any case you'll have to do 2 adds because DateAdd does not support BIGINT so you'll have to split it up. Perhaps a user defined function (which I believe Synapse also supports) would be a better solution here.

Share via

Unix time stamp conversion in Azure synapse analytics

Read from existing internal table

Show contents of the dataframe

Your answer