topic Re: Convert String to Timestamp in Data Engineering

Convert String to Timestamp

vaio — Sat, 18 Nov 2017 18:58:18 GMT

I have a dataset with one column of string type ('2014/12/31 18:00:36'). How can I convert it to timastamp type with PySpark?

Re: Convert String to Timestamp

vaio — Sun, 19 Nov 2017 21:26:10 GMT

I am trying to do it in this way, however, the result is null.

df2 = df.select(col('starting_timestamp'), df.starting_timestamp.cast('timestamp').alias('time'))

+-------------------+----+
| starting_timestamp|time|
+-------------------+----+
|2015/01/01 03:00:36|null|
|2015/01/01 03:01:06|null|
|2015/01/01 03:01:12|null|
|2015/01/01 03:01:20|null|
|2015/01/01 03:01:27|null|
+-------------------+----+
only showing top 5 rows

Re: Convert String to Timestamp

vaio — Sun, 19 Nov 2017 21:42:29 GMT

I found the solution. It is as follows:

df2 = df.select('ID', 'starting_timestamp', unix_timestamp('starting_timestamp', "yyyy/MM/dd HH:mm:ss") .cast(TimestampType()).alias("timestamp"))

Re: Convert String to Timestamp

hariumesh — Mon, 25 Mar 2019 07:21:36 GMT

Iam facing the same problem with the Pyspark where iam getting null after change to timestamp.The data set similar to above with some additional column

df2 = df.select('Customer', 'Transaction_Timestamp','Transaction_Base_Point_Value', unix_timestamp('Transaction_Timestamp', "yyyy/MM/dd HH:mm:ss") .cast(TimestampType()).alias("timestamp"))
|-- Customer: string (nullable = true)
 |-- Transaction_Timestamp: string (nullable = true)
 |-- Transaction_Base_Point_Value: integer (nullable = true)
 |-- timestamp: timestamp (nullable = true)
 
But output of timestamp column return null

Re: Convert String to Timestamp

mathan_pillai — Tue, 26 Mar 2019 12:29:30 GMT

Hi,

It is strange that it returns null. It works fine for me in pyspark as well. Could you please compare the code? Also try displaying the earlier dataframe. pls make sure that the values in original dataframe are displaying properly and are in appropriate datatypes (StringType).

```

from pyspark.sql.functions import unix_timestamp, col

from pyspark.sql.types import TimestampType

from pyspark.sql.types import StringType

df = spark.createDataFrame(["2015/01/01 03:00:36"], StringType()).toDF("ts_string")

df1 = df.select(unix_timestamp(df.ts_string, 'yyyy/MM/dd HH:mm:ss').cast(TimestampType()).alias("timestamp"))

df1.show()

```

If it still doesn't resolve, please share the full code, including how you are creating the original dataframe. Please let us know how it goes.

Thanks

Re: Convert String to Timestamp

Doha — Wed, 08 May 2019 13:49:51 GMT

i have Spark 1.6.0 on Cloudera 5.13.0

i have the same problem and this is my full code , please help me

this is the format of my row : 25/Jan/2016:21:26:37 +0100

from pyspark.sql import HiveContext

from pyspark.sql.functions import unix_timestamp, col

from pyspark.sql.types import TimestampType

from pyspark.sql.types import StringType

SQLContext = HiveContext(sc)

df=sqlContext.sql("select * from test.test")

df1 = df.select(unix_timestamp(df.date_hour, 'yyyy/MM/dd:HH:mm:ss').cast(TimestampType()).alias("timestamp"))

df1.show()

it still null

Re: Convert String to Timestamp

gideon — Thu, 19 Sep 2019 09:31:20 GMT

hope you dont mind if i ask you to elaborate further for a shaper understanding? see my basketball court layout at https://www.recreationtipsy.com/basketball-court/