timefromparts in databricks

Shambhu Rai 1,406 Reputation points
2023-11-22T07:30:24.16+00:00

Hi Expert,

Sql server query:

create table table12(col varchar(25),col1 nchar(1000))

insert into table12 values('0','620')

select TIMEFROMPARTS (620/100, 620%100,0,0,0) ,620 as col1 from table12

Expected output::

06:20:00 620

How we can get this field in databricks or adf

Azure Data Lake Storage
Azure Data Lake Storage
An Azure service that provides an enterprise-wide hyper-scale repository for big data analytic workloads and is integrated with Azure Blob Storage.
1,416 questions
Azure Data Explorer
Azure Data Explorer
An Azure data analytics service for real-time analysis on large volumes of data streaming from sources including applications, websites, and internet of things devices.
504 questions
Azure Databricks
Azure Databricks
An Apache Spark-based analytics platform optimized for Azure.
2,059 questions
Azure Data Factory
Azure Data Factory
An Azure service for ingesting, preparing, and transforming data at scale.
10,072 questions
{count} votes

Accepted answer
  1. Amira Bedhiafi 19,056 Reputation points
    2023-11-22T15:42:19+00:00

    In Azure Databricks there isn't a direct equivalent to SQL Server TIMEFROMPARTS function. However, you can achieve a similar result by using Spark SQL. Create a DataFrame similar to your table12 and insert the values.

       from pyspark.sql import SparkSession
       from pyspark.sql.functions import col
       # Initialize Spark Session
       spark = SparkSession.builder.appName("Time Conversion").getOrCreate()
       # Create DataFrame
       data = [("0", "620")]
       df = spark.createDataFrame(data, ["col", "col1"])
    

    Then you'll need to extract the hour and minute parts from the col1 field and then format these into a time string.

       from pyspark.sql.functions import format_string
       # Convert and format time
       df_time = df.withColumn("hour", (col("col1").cast("int") / 100).cast("int")) \
                   .withColumn("minute", (col("col1").cast("int") % 100).cast("int")) \
                   .withColumn("formatted_time", format_string("%02d:%02d:00", "hour", "minute"))
    

    Then, display the result.

     df_time.select("formatted_time", "col1").show()
    
    1 person found this answer helpful.
    0 comments No comments

0 additional answers

Sort by: Most helpful