CODEWITHSUNDEEP
bashhivehiveql
Alvaro Silvino
Alvaro Silvino

Reputation: 9733

How create table with all days of year using a start_date variable?

I'd like to create a table with all the days of a year using HQL. I already tried this approach :

generate days from date range

but the hql is a bit different from sql What's the best approach?
Using the PL/HQL or using bash script and importing ?

Expected result:

start_date = 2017-02-14;

| date     |
|2017-02-14|
|2017-02-13|
|2017-02-12|
|2017-02-11|
|2017-02-10|
|2017-02-09|
|2017-02-08|
|2017-02-07|
....

Thanks

Upvotes: 2

Views: 111

Answers (2)

Juan Diego Godoy Robles
Juan Diego Godoy Robles

Reputation: 14945

Using bash and an older start date for testing purposes:

start_date="2014-02-14"
days=$((($(date -u +%s) - $(date -ud $start_date +%s))/60/60/24))
(( day_end = 366 + days ))

while (( days < day_end  ));do
  date "+%Y-%m-%d" -d "$days days ago"
  (( days++ ))
done

Result

2014-02-14
2014-02-13
2014-02-12
2014-02-11
2014-02-10
2014-02-09
2014-02-08
2014-02-07
2014-02-06
2014-02-05
2014-02-04
...
...
...
2013-02-21
2013-02-20
2013-02-19
2013-02-18
2013-02-17
2013-02-16
2013-02-15
2013-02-14

Upvotes: 1

David דודו Markovitz
David דודו Markovitz

Reputation: 44921

set start_date=2017-02-14;

select  date_sub('${hiveconf:start_date}',i)
from    (select 1 as n) dummy lateral view posexplode(split(space(364),' ')) p as i,x
;

2017-02-14
2017-02-13
2017-02-12
2017-02-11
2017-02-10
.
.
.
2016-02-20
2016-02-19
2016-02-18
2016-02-17
2016-02-16

Upvotes: 2

Related Questions