Scala: Generate dates in a dataframe

Question

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.....
    +--------------------+

sachav · Accepted Answer · 2019-08-25 12:45:25Z

1

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

answered Aug 25, 2019 at 12:45

sachav

1,3168 silver badges11 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

firsni · Accepted Answer · 2019-08-25 13:38:55Z

0

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")

answered Aug 25, 2019 at 13:38

firsni

9366 silver badges14 bronze badges

Collectives™ on Stack Overflow

Scala: Generate dates in a dataframe

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related