Default date format

Answered

 Hi!

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

8
15 comments

Hello, 

 

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),
conId INTEGER,
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".

4
Avatar
Permanently deleted user

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.

2

I am facing the same issue !

1

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

0

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. 

0
Avatar
Permanently deleted user

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.

0
Avatar
Permanently deleted user

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.

0
Avatar
Permanently deleted user

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).

0
Avatar
Permanently deleted user

I am facing the same issue !

 

0

Also, there is an feature request similar to your descriptions: https://youtrack.jetbrains.com/issue/DBE-3906

 

0

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

0

Vasily Chernov, it's not related.

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

0
Avatar
Permanently deleted user

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.

0
Avatar
Permanently deleted user

I created an Issue for this here: https://youtrack.jetbrains.com/issue/DBE-7902

0

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.

0

Please sign in to leave a comment.