CODEWITHSUNDEEP
sqlhivehiveql
Memphis Meng
Memphis Meng

Reputation: 1661

Compare timestamp and string in Hive QL

There is a dataset containing some values that are not desirable: for example, they are missing and shown in '1970/01/01 00:00:00' as the placeholder.

To make sure the query is what I want, how should I filter on this column (timestamp data type)? Or I should put: where does it go wrong if I wrote:

select * from table where timestamp_col <> '1970-01-01 00:00:00'

Upvotes: 1

Views: 145

Answers (1)

leftjoin
leftjoin

Reputation: 38290

cast string to timestamp and you will see. In some Hive versions timestamp requires also 0 milliseconds to be added:

select timestamp('1970-01-01 00:00:00.0')

check that it returns timestamp, not NULL and use in the filter

Upvotes: 1

Related Questions