Question: 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?](https://stackoverflow.com/questions/30081369/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.

Answer: 
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](https://docs.oracle.com/javase/7/docs/api/java/text/SimpleDateFormat.html) for reference
unix\_timestamp(`timestamp`,"yyyy-MM-dd'T'hh:mm:ss.SSSX") works