Discussions

Expand all | Collapse all

JDBC Client is showing Time in GMT

  • 1.  JDBC Client is showing Time in GMT

    Posted 29 days ago
    Hi,

    we have inserted a record a Table and when we are selecting the omnisql then getting the exact Time Value.

    Query 1: Same when fetching using JDBC client, response is in GMT.
    Query 2: Observed Extra 0 in response. (Mentioned below in Red Colour) 

    omnisql> select * from DBDATE_CHECK;
    gendate
    2019-08-08 14:00:00
    --------------------------------------------------
    JDBC RESPONSE
    response
    BR === CREATE TABLE DBDATE_CHECK (gendate TIMESTAMP(0));insert into DBDATE_CHECK(gendate) values('2019-08-08 14:00:00');
    Date Output : 2019-08-08 19:30:00.0

    ---------------------------------------------------
    JDBC Query

    lSql ="CREATE TABLE DBDATE_CHECK (gendate TIMESTAMP(0));insert into DBDATE_CHECK(gendate) values('2019-08-08 13:10:10');";

    System.out.println("TBR === " + lSql);
    lSql = "SELECT gendate from DBDATE_CHECK";

    lRes = stmt.executeQuery(lSql);
    while (lRes.next())
    {
    evrDetList.add(lRes.getString(1));
    System.out.println("Date Output : " + lRes.getString(1));
    ------------------------------------------------------------

    #Core


  • 2.  RE: JDBC Client is showing Time in GMT

    Posted 29 days ago
    Hi @Sumit Srivastava,

    All the time date datatypes on omnisci database are stored as UTC and without any info about the timezone; JVM is unaware of that so when you use the toString method the local  timezone is used leading to a something that looks like a wrong result, while omnisql tool looks right.

    To correct this behavior you have two ways; the first one is to extract dates, times and ts with the method that has the calendar attribute and the second one is to set the JVM on gm timezone.

    We discussed this issue on this community thread

    https://community.omnisci.com/communities/community-home/digestviewer/viewthread?MessageKey=0054e013-d0f3-4271-8191-86dcb6f39cc8&CommunityKey=d06df790-8ca4-4e54-91a0-244af0228ddc&tab=digestviewer#bm0054e013-d0f3-4271-8191-86dcb6f39cc8Hi


  • 3.  RE: JDBC Client is showing Time in GMT

    Posted 28 days ago
    Hi @Candido Dessanti

    we have managed this in JDBC as below

    import java.util.TimeZone;
    TimeZone.setDefault(time)
    Zone.getTimeZone("UTC"));

    However still getting a extra Zero in response as Date Output : 2019-08-08 19:30:00.0

    Regards,
    sumit 



  • 4.  RE: JDBC Client is showing Time in GMT

    Posted 28 days ago
    So the datetime returned is the one expected and you are getting an extra 0?
    I will check this out later, but I am sure the driver is just applying a toString to the timestamp.


  • 5.  RE: JDBC Client is showing Time in GMT

    Posted 27 days ago
    Hi @Sumit Srivastava,

    as I anticipated yesterday the getString in the RS is calling a java toString method, so it' out of control of database or driver; you are getting a ".0" because the java timestamp datatype has a resolution to milliseconds, so the toString append the milliseconds (in you case 0 because you defined you filed with timestamp(0)).

    anyway you can format the returned Timestamp with class (you can also specify the TZ)

    e.g.

          sql = "SELECT * from timestamp_test";
          rs = stmt.executeQuery(sql);
    
          SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss"); 
          sdf.setTimeZone(TimeZone.getTimeZone("UTC"));
    
          while (rs.next()) {
        	  
        	System.out.println("sdf:"+sdf.format(rs.getTimestamp(1)));
        	System.out.println("getString:"+rs.getString(1));
          
          }

    the output would be something like that (I'm UTC+1)

    sdf:       2011-01-02 10:50:13
    getString: 2011-01-02 11:50:13.0
    sdf:       2013-11-12 20:45:21
    getString: 2013-11-12 21:45:21.0