CODEWITHSUNDEEP
sqltimehive
Aavik
Aavik

Reputation: 1037

unix_timestamp for 2019-03-31T15:29:42.443-07:00

I am running hive query to convert string to timestamp format:

References from:

  1. https://help.sumologic.com/03Send-Data/Sources/04Reference-Information-for-Sources/Timestamps%2C-Time-Zones%2C-Time-Ranges%2C-and-Date-Formats
  2. how can I check a string of datetime in hive whether meet requirement?

Query:

select `timestamp`, unix_timestamp(`timestamp`,"yyyy-MM-dd'T'hh:mm:ss'X'")
from tbl1
where eventdate="2019-03-31"
limit 2;

Result:

2019-03-31T15:29:42.443-07:00   NULL
2019-03-31T13:35:10.929-07:00   NULL

I tried:

unix_timestamp(`timestamp`,"yyyy-MM-dd'T'hh:mm:ssX")
unix_timestamp(`timestamp`,"yyyy-MM-dd'T'hh:mm:ss:SSSX")
unix_timestamp(`timestamp`,"yyyy-MM-dd'T'hh:mm:ss:SSSZZZZ")
unix_timestamp(`timestamp`,"yyyy-MM-dd'T'hh:mm:ss:SSSZZ:ZZ")

I am getting NULL but not the timestamp converted.

Thanks.

Upvotes: 0

Views: 144

Answers (2)

leftjoin
leftjoin

Reputation: 38290

Correct format is

"yyyy-MM-dd'T'HH:mm:ss.SSSX"
  • you have 24HRS, not 12 am/pm, it corresponds to HH, not hh. See SimpleDateFormat for reference

Upvotes: 2

Aavik
Aavik

Reputation: 1037

unix_timestamp(timestamp,"yyyy-MM-dd'T'hh:mm:ss.SSSX") works

Upvotes: -1

Related Questions