CODEWITHSUNDEEP
sqldatetimehive
Jerry
Jerry

Reputation: 175

HIVE. SQL. Calculating time difference between string

I have data about users showing up online. In my query I need to select only those between 13:00:00 and 14:00:00.

The data rows about time look like:

170214074534 where it is YYMMDDHHMMSS - 14 February 2017, 07:45:34

Can you help me with the query part please?

I think it should be easier to find a way without converting it to timedate format. Another way seems to be to ignore first 6 symbols and select data between 130000 and 135959.

Upvotes: 0

Views: 358

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269463

You can use string functions for this:

where substr(col, 7, 2) = '13'

I would also suggest that you fix your data format. That is an arcane way of storing date/time values.

Upvotes: 1

Related Questions