Is there a milliseconds equivalent for second(), minute(), hour(), etc. in Hive SQL? If not, how can I extract just the milliseconds from a date?

user_13681075 0
Viewed 65

I have data like this:

2019-12-11 23:38:26.583

And I want to receive back this:

583

i.e. just the milliseconds part.

In Hive SQL, there are functions like second(), hour(), minute(), etc. that let you extract these elements very easily, but I don't see one for millisecond(). And I tried it and it doesn't do anything, i.e. it doesn't exist. How can I accomplish this then?

Thank you :)

hiveql hive sql
2 Answers

You can use date_format:

select date_format('2019-12-11 23:38:26.583', 'SSS');

Note that this returns string type. If you want int type, you can cast accordingly:

select cast(date_format('2019-12-11 23:38:26.583', 'SSS') as int);
user_141657300

One more method using regexp_extract:

select regexp_extract('2019-12-11 23:38:26.022', '\\.(\\d+)$',1);

Result:

022
user_27003440
Related