CODEWITHSUNDEEP
apache-sparkapache-spark-sql
Praveena Gunasekera
Praveena Gunasekera

Reputation: 79

Getting the week number of month using time stamp format '2016-11-22 14:35:51' using SPARK SQL

I have a time stamp column which has records of type '2016-11-22 14:35:51' in SPARK SQL. Would someone help me with retrieving the week number of month for the given time stamp format?

I have tried,

SELECT timestamp, DATE_FORMAT(timestamp, 'u') AS WEEK FROM table_1;

But it gives the wrong output as,

timestamp  |  WEEK
2016-11-22 |  2

Appreciate if someone coulkd help me out.

Thanks.

Upvotes: 1

Views: 6163

Answers (1)

eliasah
eliasah

Reputation: 40370

You are using the wrong literal, you need to use W instead of u.

The literal u refers to the day number of week.

scala> sqlContext.sql("select current_timestamp() as time, date_format(current_timestamp,'W') as week").show
// +--------------------+----+
// |                time|week|
// +--------------------+----+
// |2017-01-09 13:46:...|   2|
// +--------------------+----+

scala> sqlContext.sql("select to_date('2017-01-01') as time, date_format(to_date('2017-01-01'),'W') as week").show
// +----------+----+
// |      time|week|
// +----------+----+
// |2017-01-01|   1|
// +----------+----+

If you have more doubts, you can always refer to the official documentation of SimpleDateFormat in Java.

Upvotes: 5

Related Questions