4

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 -

enter image description here

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

Ole V.V.
  • 81,772
  • 15
  • 137
  • 161
a13e
  • 838
  • 2
  • 11
  • 27
  • 2
    Possible duplicate of [converting date time to 24 hour format](https://stackoverflow.com/questions/6842245/converting-date-time-to-24-hour-format) – Raman Mishra Oct 09 '18 at 19:20
  • 1
    Which data base are you using? – Raman Mishra Oct 09 '18 at 19:26
  • @RamanMishra I am using MemSQL database. – a13e Oct 09 '18 at 19:29
  • 1
    Hmmmm, it looks like a duplicate BUT the [HH](http://joda-time.sourceforge.net/apidocs/org/joda/time/format/DateTimeFormat.html) is correct for 0-23. it's going to help here to give some PM example of DateTime's that are formatted to 12 hours earlier. – Andy Hayden Oct 09 '18 at 19:44
  • Are you sure you weren't using hh? I tried this and it works as expected. – Andy Hayden Oct 09 '18 at 19:46
  • can you try this: val date: String =new Instant(orderTs).toDateTime(DateTimeZone.UTC).toString("yyyy-MM-dd HH:mm:ss") – Raman Mishra Oct 09 '18 at 19:58
  • @AndyHayden yes. I am absolutely sure. I have not been using `hh` as that would keep my GMT date's hour value ranging between 0 to 12. I have been using only `HH`. But there is nowhere my `order_dt_utc` crossing **12:59:59**. – a13e Oct 09 '18 at 20:44
  • I get: `2018-10-09 20:54:03` with `new Instant().toDateTime(DateTimeZone.UTC).toString("yyyy-MM-dd HH:mm:ss")` – Andy Hayden Oct 09 '18 at 20:54
  • @RamanMishra No instant converter found for type: java.lang.Integer – a13e Oct 09 '18 at 21:03
  • hmmm, looks like I got where the problem is here. It wasn't probably the issue of the DateTime() function or string pattern. I used a logger and tried printing my database `record` right before I prepare my sql statement, add, execute and commit batch. Because the record that is going towards database holds the exact the value that is desired in 24 hours format. Can we somehow know what format is being sent to db? – a13e Oct 09 '18 at 22:35
  • I suspect that the issue is either in the presentation of data from the database or in storing the data into the database. Maybe you want to share the code you use for the latter? – Ole V.V. Oct 10 '18 at 05:13
  • If you save a time of 00:01:02, will it also come out as 12:01:02 (AM)? Could you try saving, say, 02:00:00 and 14:00:00 and test in SQL whether they are equal? What happens if you select `date_format(order_dt_utc, '%H')` from the table? Also, which time zone are you in? Finally, why are you saving redundant information to the database, is this just to research and demonstrate the problem? – Ole V.V. Oct 10 '18 at 05:27

1 Answers1

0

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.

Hassan Ubaid
  • 159
  • 1
  • 4