Default date format



Is there a way to set the default date format for queries and results?




I have a related question to this problem.

I am running datagrip 2018.1.1 on ubuntu 16.04 64 bit.

I have an SQLITE table with a datetime field. The create command is the following:

create table MarketDataInfoTable
tableName VARCHAR(1000) not null
primary key,
description VARCHAR(10000),
symbol VARCHAR(1000),
currency VARCHAR(1000),
exchange VARCHAR(1000),
category VARCHAR(1000),
secType VARCHAR(1000),
earliestDateTime DATETIME

(I am creating this table using python sqlalchemy)

This table looks like this:

Note the format of the datetime displayed in the last column.
I specified the datetime format in the preferences of this sqlite database like this:

I applied the settings and restarted datagrip. The display is still the same.
I also set the same settings in the settings for sqlite datasources:

I applied, saved, restarted datagrip. The date is still displayed in the same way.

Now the real problem (I believe it is connected to the above problem).

When I change the date in datagrip, I am presented with the following popup:

I change the date to a different time (say 05:30:00)
As a result, the date is now displayed in a different format (still not the one that I specified in the settings, but the one that is set by default according to the docs):

I then want to persist this changed date to the table on disk.

For this to happen, I click the icon with the green arrow and "DB":

Now the strange thing happens. The date is converted to something else (presumably a number):

From now on, I am unable to do anything with that entry. It is not interpreted as a date:

My underlying program actually stops working as the sqlalchemy class tries to access a string in that place, but it is a number.
Whatever string I enter in this field (that looks like a date) is converted to a number.
The number seems to be milliseconds since epoch since the string "1970-01-01 00:00:01" is converted to the number 1000.

The same problem does not exist when I use sqlitebrowser. In this program, I can edit the datetime just fine and nothing breaks.

So I wonder if someone knows what I can do make datagrip not transform dates to numbers.


Note that the conversion to the number only takes place for strings that look like a datetime. the string "dummy" for example stays the string "dummy".


I followed Valeriy's lead and went into the date property for the data connector. Once I stopped the connection and restarted, it worked just fine.


I am facing the same issue !


Wanted feature! Because our team is migrating from another tool which offered different date formats... 


PyCharm 2017.3. Querying SQLite database. For datetime fields PyCharm Database Console used format "yyyy-MM-dd". Therefore for datetimefields time part is missed in the Database Console. I tried to change data source | Properties | Advanced | date_string_format to something including time part, but result still the same. 


Hey guys, did anyone fine a solution to this? Coming from Oracle SQL Developer, I'm accustomed to date formats of "DD-MON-YY" and also the option of  being able to set whichever date format I desire. Not being able to set the specific date format in Datagrip is one of its annoyances compared to Developer.


Thanks but the default format that I see is what you have in the attached. I tried added date_string_format as a user defined option with the specific format that I wanted using java and oracle syntax but still no luck. It's still showing as the default one.


Looks like mentioned fixes related to SQLite related data source only, not applicable for sql server in DataGrip(at least DataGrip 2018.1.1, Build #DB-181.4668.2 version).


I am facing the same issue !



Also, there is an feature request similar to your descriptions:



Experiencing same issue. Can't work with sqlite datetime fields inside pycharm!


Vasily Chernov, it's not related.

This thing annoys me for a year already! Fix it already!


Also experiencing this with DataGrip/PyCharm and our SQLite (sqlalchemy) table. Annoyingly, if I paste the SQL into sqlite itself, or into a DataGrip Console, it works fine:


UPDATE "tablename" SET "myfieldname" = '2019-02-09 19:09:10.773' WHERE "id" = 1;


It's just the table-view that's annoying.


I had the same issue. As Mitchell Ludwig said in the comments of the issue change the date_class to TEXT and set the date_string_format to yyyy-MM-dd HH:mm:ss.SSSSSS.


Please sign in to leave a comment.