How to convert ISO Date to UTC date in Hive

by

Hive understands this format: 'yyyy-MM-dd HH:mm:ss.SSS'.

Use unix_timestamp() to convert to seconds passed from 1970-01-01, then use from_unixtime() to convert to proper format: 

 select from_unixtime(UNIX_TIMESTAMP("2017-01-01T05:01:10Z", "yyyy-MM-dd'T'HH:mm:ss'Z'"),"yyyy-MM-dd HH:mm:ss");

Result:

2017-01-01 05:01:10

Update. This method is to remove Z and replace T with space using regexp_replace and convert to timestamp if necessary, without using unix_timestamp(), this will preserve milliseconds:

select timestamp(regexp_replace("2019-05-17T17:03:09.775Z", '^(.+?)T(.+?)Z$','$1 $2'));

Result:

2019-05-17 17:03:09.775

More Related Contents:

  1. Change date format as dd/mm/yyyy in php [closed]
  2. How can I parse / create a date time stamp formatted with fractional seconds UTC timezone (ISO 8601, RFC 3339) in Swift?
  3. Parsing RFC-3339 / ISO-8601 date-time string in Go
  4. `uuuu` versus `yyyy` in `DateTimeFormatter` formatting pattern codes in Java?
  5. group by dates in mongodb
  6. Format date as dd/MM/yyyy using pipes
  7. How to convert milliseconds into human readable form?
  8. Zero-based month numbering [closed]
  9. Convert normal date to unix timestamp
  10. Is it always a good idea to store time in UTC or is this the case where storing in local time is better?
  11. Date does not display from Model on HTML input type date
  12. PostgreSQL: how to convert from Unix epoch to date?
  13. How to convert .txt file to Hadoop’s sequence file format
  14. VBScript How can I Format Date?
  15. Google Apps Script date format issue (Utilities.formatDate)
  16. Determine season given timestamp in Python using datetime
  17. Difference between two dates expressed as years, months, days (in one column)
  18. How to compare dates or date against today with query on google sheets?
  19. Date arithmetic in cmd scripting
  20. Convert string to datetime
  21. Changing date format to “%d/%m/%Y”
  22. breezejs: date is not set to the right time
  23. Compare two dates Google apps script
  24. How do I align date and values to gridlines in Google Chart?
  25. Carbon Difference in Time between two Dates in hh:mm:ss format
  26. Convert string into Date type on Python [duplicate]
  27. Same date in different time zone
  28. What is the precise definition of JDE’s Julian Date format?
  29. How can I work with dates before 1900 in PHP?
  30. efficiently generate a random sample of times and dates between two dates

Leave a Comment