CODEWITHSUNDEEP
sqlscaladateapache-spark-sql
John
John

Reputation: 79

Scala: Generate dates in a dataframe

I want to create a dataframe in Scala with all dates from a start date to an end date.

I can get an Array of dates using this expression

    val dates = sqlContext.sql("SELECT sequence(to_date('2018-01-01'), to_date('2018-02-01'), interval 1 day) as dates")

    +--------------------+
    |               dates|
    +--------------------+
    |[2018-01-01, 2018...|
    +--------------------+

This gives me a dataframe with one column and one row. I rather want one row for each date and not an array with all the dates as I have now

How can I achieve this?

    +--------------------+
    |               dates|
    +--------------------+
    |[2018-01-01|
    |[2018-01-02|
    |[2018-01-03|
    |[2018-01-04|
    etc.....
    +--------------------+

Upvotes: 0

Views: 1470

Answers (2)

firsni
firsni

Reputation: 906

You can create a list of dates and then convert it to a dataframe like this.

import org.joda.time.LocalDate

val start = new LocalDate(2019, 9, 24)
val end   = new LocalDate(2019, 9, 28)
val numberOfDays = Days.daysBetween(start, end).getDays
val days = (for (f<- 0 to numberOfDays) 
yield  start.plusDays(f).toDate.toString).toList
import session.implicits._
val df = days.toDF("dates")

Upvotes: 0

sachav
sachav

Reputation: 1316

You can use explode to unwind the array:

SELECT explode(sequence(to_date('2018-01-01'), to_date('2018-02-01'), interval 1 day)) as dates

Upvotes: 1

Related Questions