2

In the retail dataset(CSV), I have a column "Day" and it has value starting from 1 to 712 (around two years of data). I want to convert it into a "yyyy-MM-dd" format. The starting timestamp is 2017-01-01.

ACTUAL:

Day, Prod_id, Sales
1, 23232, 34.5
1, 34522, 76
2, 53436, 45.7
2, 67353, 12.5
.....
.....
712, 35357, 67.2

EXPECTED:

Day, Prod_id, Sales, TS
1, 23232, 34.5, 2017-01-01
1, 34522, 76, 2017-01-01
2, 53436, 45.7, 2017-01-02
2, 67353, 12.5, 2017-01-02
.....
.....
712, 35357, 67.2, 2018-12-25

Please help me to achieve this.

leftjoin
  • 36,950
  • 8
  • 57
  • 116
Sudha
  • 137
  • 1
  • 1
  • 6
  • Does this answer your question? [SQL Server 2005 Using DateAdd to add a day to a date](https://stackoverflow.com/questions/167491/sql-server-2005-using-dateadd-to-add-a-day-to-a-date) – JeffUK Aug 13 '20 at 10:04
  • 1
    Marked as duplicate, you can use the DateAdd function to add $day-1 days onto 2017-01-01 – JeffUK Aug 13 '20 at 10:04
  • similar to what @Lamanus pointed, `df.withColumn("TS",F.to_date(F.lit("2017-01-01"))).withColumn("TS1",F.expr("date_add(TS,Day)"))` make sure to subtract 1 from Day column first, this would work as per each row Day value – anky Aug 13 '20 at 10:14
  • Thanks, everyone. The below hive query solved my problem. – Sudha Aug 13 '20 at 10:45

1 Answers1

1

In Hive use date_add('2017-01-01', day) as ts

leftjoin
  • 36,950
  • 8
  • 57
  • 116