Scala/Java joda.time not converting date in 24 hours format

Question

I am trying to convert a long utc value into "yyyy-MM-dd HH:mm:ss" formatted pattern. I am expecting my data to be converted on 24 hours range scale and in GMT. My code passes all the test cases, I push the data into database using the jar that is newly built with this code -

dbRecord("order_dt_utc") = if (orderTs.isDefined) Some(new DateTime(orderTs.get, DateTimeZone.UTC).toString("yyyy-MM-dd HH:mm:ss")) else None

and now, when I query my database, I find that the data is still converting on 12 hours range. The query -

SELECT order_id, order_dt, order_dt_utc, order_ts_utc, from_unixtime(order_ts_utc/1000) FROM order_items where order_dt >= '2018-08-01' AND order_dt <= '2018-08-02' ORDER BY order_dt_utc LIMIT 1000;

And you can see the the values are not matching in the columns from_unixtime(order_ts_utc/1000) and order_dt_utc -

I am not able to figure the reason for this behaviour.

Answer 1

To convert Time Zone use the function first: CONVERT_TZ (dateobj, oldtz, newtz)

After that use the date_format function:

date_format(from_unixtime(order_ts_utc), '%Y-%m-%d %H:%i:%s');

to format your time to 00-23 format.