Hive Timestamp column, default query results behavior, common misunderstanding and formatting to a target zone

    Similar to many RDBMS, Hive Timestamp datatype and it is stored as bigint and are stored as an offset from the UNIX epoch. Usually Timestamps are interpreted to be timezone-less. However end user runs a Hive query (via Hue or any tools), it returns the columns in the default server time zone. (in our case, EST ad team started saying your app is   not saving data in UTC, we need UTC format etc. etc.  After spending sometime with Hive language manual and with some of the past issues, I found an work around via Hive JIRA.

Idea of this query is vehicle sensor data is stored in data table.

Intent of the query is to know what is the value of a sensor on a given day.

select  ID, element_cd,

from_utc_timestamp(to_utc_timestamp(from_unixtime(unix_timestamp(timstm_column) ),'EST'), 'UTC'),

element_val

from  data_elements

where yr_nbr = 2019 and mth_nbr = 11 and day_nbr in (30)

and element_cd='POWER_LEVEL'

Lot’s of indirection. i.e. taking timestamp column & formatting it to unix timestamp and then first converting to default time zone and then to target time zone.  ( Above query returns timestamp value as UTC formatt.  My  HIVE servers are running in EST)

A diversified portfolio: 5% Inflation-Adjusted Income & moderate growth For Life

Usual concept is to hold 60% in stocks and 40% in bonds. While there are different methods/selections in to which stocks/bonds, I am using following allocations in 401K for each ongoing monthly contributions. Following are list of funds with contribution amounts.

     VEMAX( Vanguard Emerging Markets Stock Index Fund Admiral Sh )  5%

   VGENX ( Vanguard Energy Fund Investor Shares )   5%

   VGHCX ( Vanguard Health Care Fund Investor Shares ) 20%

   VGSLX ( Vanguard Real Estate Index Fund Admiral Shares) 10%

   VFIAX ( Vanguard 500 Index Fund Admiral Shares) 20%

   VWINX    Vanguard Wellesley Income Fund Investor Shares  (20%)

   VUSTX   Vanguard Long-Term Treasury Fund Investor Shares (20%)

No	Fund name	Annual dividends
1	VEMAX	4
2	VGENX	4
3	VGHCX	10
4	VGSLX	6
5	VFIAX	6
6	VUSTX	4

                                         Total 34/6 ~6%

Again I am not reinvesting all dividends automatically. All dividends sits in Vanguard money market account  with 1.X% monthly return. Again my intent is use this money in case of a deep corrections in the markets.  So far, last year above portfolio gave 5K dividends and moderate growth of 8%. Again last year is an exception with all time highs, but for past decade, my other portfolio is returning above average returns.  In my other portfolio, I am using 80% stocks and 20% bonds. ( other one is IRA and I am using little bit individual stocks too.  But same set of funds with different compositions.

The possible range of expected annual portfolio returns for the given period is 7.11% to 12.15%. 

I am not a financial analyst. I am not an expert in money, monetary policy, investment theories, theorems. My blog article should not be taken as advice, financial or otherwise, or as a statement of the right or proper way to do things. What I am is a regular IT guy, middle-aged, with a life full of past mistakes, most of which, I hope, I have learned enough from to continue forward in a positive direction. 

A long term IRA Portfolio Journal

I made plenty of mistakes my first year, primarily through trading and not investing. Again I joined stock markets race during Y2k melt-up situations. Everyone is talking about stocks during break time & this is first job in USA & took trading route. Just to make some quick bucks here.  But after 15K losses, With marriage, my philosophy has changed. All investing is limited to spouse IRA and my 401K only.  It essentially remains thus: build a core of 5 diversified funds with low expense ratio and 10 dividend paying equities, reinvest the dividends and allow them to grow. I don't have a lot of hard and fast rules for my holdings other than they must pay at least 5%. Also if a stock/fund give more than 25% of profits, sell 50% of position and lock the profits.

Currently, the retirement Portfolio consists of five funds and 20 odd stocks. Most of the stocks picks are 5 years old. I am holding some of these funds for 15+ years.

List of funds are:

Ø  VEMAX( Vanguard Emerging Markets Stock Index Fund Admiral Sh )

Ø   VGENX ( Vanguard Energy Fund Investor Shares )

Ø   VGPMX ( Vanguard Global Capital Cycles Fund )

Ø   VGHCX ( Vanguard Health Care Fund Investor Shares )

Ø   VGSLX ( Vanguard Real Estate Index Fund Admiral Shares)

Ø  VFIAX ( Vanguard 500 Index Fund Admiral Shares)

List of stocks includes

BEP

BIP

AMZA

REML

DX

CHI

GUT

ARCC

CII

EXG

APLE

BGR

RRC

PCI

AM

VEON

BGCP

AWP

CHMI

AMLP

GDX

I am not a financial analyst. I am not an expert in money, monetary policy, investment theories, theorems. My blog article should not be taken as advice, financial or otherwise, or as a statement of the right or proper way to do things. What I am is a regular guy, middle-aged, with a life full of past mistakes, most of which, I hope, I have learned enough from to continue forward in a positive direction. I am certainly not an expert in anything, but I am willing to learn, to ask right questions and take some risks.

At the end of December, the IRA is 20 years & I portfolio returned ~4.5% over last 20 years. But my point with this blog spot is long term view really works.  Regularly invest from paycheck and diversify and stay calm and ignore all the noise. Also always keep 5% of money in cash form. Sometime, if you find real value in anything, this helps.