Shulou(Shulou.com)06/01 Report--

This article mainly introduces what the Hive commonly used date function has, which has a certain reference value, interested friends can refer to, I hope you can learn a lot after reading this article, the following let the editor take you to understand it.

1. Current date and time select current_timestamp

-- 2020-12-05 19 purl 16 purl 29.284

two。 Get the current date, which is currently 2020-12-05SELECT current_date

# # OR

SELECT current_date ()

-- 2020-12-05

3. Get the timestamp SELECT UNIX_TIMESTAMP () under the unix system

-- 1524884881

4. Currently it is 2020-12-05select substr (current_timestamp, 0,10)

-- current_timestamp

5. Currently it is 2020-12-05select date_sub (current_date, 1)

-- 2020-12-04

6.yyyy-MM-dd HH:MM:ss intercept date select to_date ("2017-10-22 10:10:10")

-- 2017-10-22

Select date_format ("2017-10-22"yyyy-MM")

-- 2017-10

7. The day difference between two dates select datediff ("2017-10-22", "2017-10-12")

-- 10

Select datediff ("2017-10-22 10:10:10", "2017-10-12 23:10:10")

-- 10

Select datediff ("2017-10-22 01:10:10", "2017-10-12 23:10:10")

-- 10

8. Time intercept select from_unixtime (cast (substr ("1504684212155", 0meme 10) as int)) dt

-- 2017-09-06 15:50:12

9. Timestamp to date

Syntax: to_date (string timestamp)

Select to_date (from_unixtime (UNIX_TIMESTAMP ()

-2018-04-28

Select FROM_UNIXTIME (UNIX_TIMESTAMP (), 'yyyy-MM-dd 10, 30 yyyy-MM-dd 00')

-2018-04-28 10:30:00

Select concat (date_sub (current_date,1),'20-30-15-00')

-- 2018-04-27 20:30:00

-- hive version 1.2.0

Select date_format (date_sub (current_date,1), 'yyyy-MM-dd 2030 current_date,1 00')

10. Date increase

Note: only two original date formats are supported: yyyy-MM-dd yyyy-MM-dd HH:mm:ss, otherwise both need date_format to transfer

Date_add

Next_day

11. Additional questions

There is an active membership table. The daily partition dimension is member id, which can be replaced by device_id. Ask how to calculate the number of active members for three consecutive days in the last seven days. The structure of the table (dws.dws_member_start_day) is shown in the following table (dt is partition, date format is yyyy-MM-dd, each partition has a unique device_id):

Device_id string

Dt string

Solution routine

1. First of all, think about the date functions datediff, date_sub/date_add that can be used.

two。 Continuous dates, continuous problems will use a ranking function, but the value of the ranking function is a numerical value, which must be mapped to the continuity of dates in order to facilitate grouping. For example, dates can be mapped to consecutive numbers, or numbers can be mapped to consecutive dates. The operation to achieve these two is through the previous combination of datedff and date_sub. The principle is that dates and dates are subtracted to get continuous integers. Integers can be subtracted from a date to get consecutive dates, where date_sub can be sorted in reverse to get consecutive dates.

3. This kind of problem can be solved by subtracting successive sorting dates or sorting id, and then grouping.

1. Add a column of sorting number SELECT device_id to the original table

Dt

Row_number () over (PARTITION BY device_id

ORDER BY dt) ro

FROM dws.dws_member_start_day

two。 Change the serial number to a consecutive date, or change the date to a consecutive number, and then become a gid-- 2.1serial number to a consecutive date.

SELECT device_id

Dt

Datediff (dt, date_add ('2020-07-20), row_number () over (PARTITION BY device_id)

ORDER BY dt)) gid

FROM dws.dws_member_start_day

-2.2 the date is changed to a serial number

SELECT device_id

Dt

(datediff (dt, '2020-07-21)-row_number () over (PARTITION BY device_id

ORDER BY dt)) gid

FROM dws.dws_member_start_day

3. Group filter SELECT device_id,count (1)

FROM

(SELECT device_id

Dt

Datediff (dt, date_add ('2020-07-20), row_number () over (PARTITION BY device_id)

ORDER BY dt)) gid

FROM dws.dws_member_start_day

WHERE datediff (dt, CURRENT_DATE) BETWEEN-7 AND 7) tmp

GROUP BY device_id

Gid

HAVING count (1) < 3

Thank you for reading this article carefully. I hope the article "what are the common date functions of Hive" shared by the editor will be helpful to you. At the same time, I also hope that you will support us and pay attention to the industry information channel. More related knowledge is waiting for you to learn!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.