sharp sharp - 1 year ago 102
SQL Question

Selecting the first day of the month in the HIVE

I am using Hive (which is similar to SQL, but the syntax can be little different for the SQL users). I have looked at the other stackoverflow, but they seems to be in the SQL with different syntax.

I am trying to the get the first day of the month through this query. This one gives me today's day. For example, if today is 2015-04-30, then result would be 2015-04-01. Thanks!

select
cust_id,
FROM_UNIXTIME(UNIX_TIMESTAMP(),'yyyy-MM-dd') as first_day_of_month_transaction
--DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0) as first_day_of_month_transaction --SQL format. Not compatible in Hive.
from
customers;

Answer Source

To get the first day of the month, you can use:

date_add(<date>,
         1 - day(<date>) )

Applied to your expression:

date_add(FROM_UNIXTIME(UNIX_TIMESTAMP(), 'yyyy-MM-dd'),
         1 - day(FROM_UNIXTIME(UNIX_TIMESTAMP(), 'yyyy-MM-dd')) 
        )

But this will work for any column in the right format.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download