2

Goal:

I have table as below. I want to get start of day of each week in 2019. And the startday of week must be monday.

week 
23
24
25
26 
...

Expected:

week   startofday
23     2019-06-03
..      ....

Trying:

I had read date_sub and date_add but could not get what I want.

Note:

My question is similar with this post. The differ is the last is solved by python.

Jack
  • 1,724
  • 4
  • 18
  • 33

3 Answers3

1

I found the next useful example, on this source and adapted for your purpose:

select 23 as weeknumber,
date_format(date_sub(from_unixtime(unix_timestamp('2018-12-29','yyyy-MM-dd')+(23*7*24*60*60)),pmod(datediff(from_unixtime(unix_timestamp('2018-12-29','yyyy-MM-dd')+(23*7*24*60*60)),'1900-01-07'),7)),"MMMMM dd,yyyy") as startday,
date_format(date_add(from_unixtime(unix_timestamp('2018-12-29','yyyy-MM-dd')+(23*7*24*60*60)),6 - pmod(datediff(from_unixtime(unix_timestamp('2018-12-29','yyyy-MM-dd')+(23*7*24*60*60)),"1900-01-07"),7)),"MMMMM dd,yyyy") as endday;

So, change the constant '23' with your column and 2018-12-28 with the end of the previous year that you need.

In a few words, the sql code makes this:

  • takes the week:23, transforms it to seconds (23*7(days have a week)*24 hours*60mins*60seconds);
  • transforms in seconds also the last day of last week of previous year (28-December-2018);
  • addition the two sums and the result will be a date;
  • having the date, we can calculate the first day of week (and last day, I know that you don't need it, but maybe someone will need it);

Now, in my example, because of my UTC, the first day of week is Sunday, not Monday as you expect!

Hope that it's what you need.

Results: results

F.Lazarescu
  • 1,385
  • 2
  • 16
  • 31
  • @F.Lazarescu This is not I want, it baesd on specific date but not the number of week. – Jack Aug 07 '19 at 06:31
  • @Jack, I just edited the answer. Please check again. – F.Lazarescu Aug 07 '19 at 07:12
  • @F.Lazarescu I have columns of number not just one number. – Jack Aug 07 '19 at 07:17
  • Yes Jack, as I wrote you, instead of '23' put your column name and then, at the end, write ".. from your_table_name". It should work. Did you tried? If you have only weeks from 2019, you need to change only the constant '23'. – F.Lazarescu Aug 07 '19 at 07:19
0

Answer Update: I was not able to do this with Hive available functions. had to write a UDF in Python.

datefunction.py:

import sys
from datetime import datetime, timedelta
try:
    for line in sys.stdin:
        week=line.strip()
        year="2019"
        newDate = datetime.strptime(year+week + ' 1', "%Y%W %w")
        my_date = newDate- timedelta(days=7)
        format_date = my_date.strftime('%Y-%m-%d')

        sys.stdout.write('\t'.join([week,str(format_date)]) + '\n')

except:
    print(sys.exc_info())

Adding this udf to Hive:

add file /your udf location path/python/datefunction.py;

created a test_week table in Hive with column week datatype as int and entered few weeks data.

Results as below:

hive> select TRANSFORM (week) USING 'python datefunction.py' As (week,startofday) from db.test_week;
Total jobs = 1
Launching Job 1 out of 1
Status: Running (Executing on YARN cluster with App id application_1563337199692_13076)

--------------------------------------------------------------------------------
        VERTICES      STATUS  TOTAL  COMPLETED  RUNNING  PENDING  FAILED  KILLED
--------------------------------------------------------------------------------
Map 1 ..........   SUCCEEDED      1          1        0        0       0       0
--------------------------------------------------------------------------------
VERTICES: 01/01  [==========================>>] 100%  ELAPSED TIME: 0.83 s
--------------------------------------------------------------------------------
OK
23      2019-06-03
24      2019-06-10
25      2019-06-17
26      2019-06-24
27      2019-07-01
28      2019-07-08
29      2019-07-15
30      2019-07-22
31      2019-07-29
32      2019-08-05
vikrant rana
  • 4,509
  • 6
  • 32
  • 72
-1

select next_day('2019-08-22', 'MONDAY');

Iamnotme
  • 31
  • 2