Discussions

Expand all | Collapse all

Date Field

Jump to Best Answer
  • 1.  Date Field

    Posted 07-01-2019 05:25

    Hello

    I am exploring the features of OMNISCI to access parquet files via JDBC driver. I see an issue  filtering DATE columns .

     

    Casting a String to date or timestamp resulted in a previous day. Getting a date part of a date column resulted in next day. So it fails in where condition of a date field while retrieving data from a table.  

     Example

    select cast('2010-10-01' as DATE), cast ('2010-10-01' AS TIMESTAMP) ,delivery_date,datepart('Year',delivery_date) , datepart('month',delivery_date) , datepart('day',delivery_date)  from  xyz_table  where  point_type ='SH'

     
    Your assistance is appreciated to understand this issue.


     


    #Connectors


  • 2.  RE: Date Field

    Posted 07-01-2019 08:49
    hi @Ravi Ramachandran,

    I see two possible problems on your query.

    1. The first one that probably isn't a problem at all is the 4 hours subtracted by the server, is perhaps caused by a different time offset between the server and the client.
    2. The second is pretty strange; which datatype are you using for the date and how do you load it? (i guess from parquet files with copy command, but I can be wrong)

    Can you share the DDL of the table, the method you used to load it and the client tool you are using to access the data (looks squirrel SQL, but I can be wrong





  • 3.  RE: Date Field

    Posted 07-01-2019 14:06

    Thanks for your response.

    The time in server and client are in sync.  Regardless of a time zone conflicts , I am expecting  the 'type cast'  of  a static date string parameter should return the same DATE value .

    Here is the DDL .

     

    CREATE TABLE xyz_table

    (

       delivery_date date,

       point_type varchar(100)

      

    )

     

     






  • 4.  RE: Date Field
    Best Answer

    Posted 07-02-2019 04:54
    @Ravi Ramachandran,

    I did some tests using the jdbc driver with squirrelsql and dbeaver, and both of them are adding an offset on dates and timestamps using the client timezone; so If your client on UTC-0400 they subtract 4 hours if UTC+0100 1 hour and so on.

    I guess this is a normal behavior of JRE they are using to run, so you should change the configuration of your tool to use UTC TZ
    With default squirrel configuration I get


    After adding the option to the .bat launching the SquirrelSql, I got the expected results


    changed the line
    start "SQuirreL SQL Client" /B "%LOCAL_JAVA%" -Duser.timezone=UTC -Dsun.awt.nopixfmt=true -Dsun.java2d.noddraw=true -cp %CP% -splash:"%SQUIRREL_SQL_HOME%/icons/splash.jpg" net.sourceforge.squirrel_sql.client.Main %TMP_PARMS%

    All the date/timestamp on our database are stored as UTC, and I'm sure the jdbc is doing nothing because I modified all the date/time/timestamp JDBC methods last February and nothing is changed since then (I also tried an older version of the driver getting the same results).
    Furthermore using other tools like Tableau, I get the correct dates.

    About the problem of the dates shifted by 1 day, it's not a problem, because the delivery_date you are seeing in the tools is being changed by the java client, so on the database is stored as '2010-12-01', '2010-12-02','2012-12-03' and when you ask for the date part the database return a number that the java client isn't changing because it's not a date.

    Could you try to change the .bat of squirrelsql, rerun the query and report back?

    thanks for your patience



  • 5.  RE: Date Field

    Posted 07-02-2019 11:54

    Your suggestion of configuring the time zone to UTC works on a) converting string to Date  b) Date Part function c) where clause on comparing a date field. Thanks a lot.