Dbeaver: Datetime 0000-00-00 00:00:00 displayed as NULL

[codetrash]

datetime columns with actual value 0000-00-00 00:00:00 (viewer with other DB editor) are displayed as NULL, so the following Query cannot work

SELECT * from my_table where lac_date IS NOT NULL

[kyleflan]

You haven't mentioned what DBMS you're working with. That would help us understand what you're trying to accomplish.

I'm going to assume MySQL, since I know MySQL uses 0000-00-00 00:00:00 (a zero date) in some instances. A zero date in MySQL is not null; it's 0. However, DBeaver represents a zero date as null in the result set by default according to the driver settings, so when you do

SELECT * FROM my_table where lac_date IS NOT NULL

you'll still see null values for all the zero dates.

One option is to right click on the connection and choose "Edit connection". Then, in the Driver properties, set the zeroDateTimeBehavior to round. This will now round all 0000-00-00 00:00:00 values to 0001-01-01 00:00:00.

Example:

CREATE TABLE test_dates (id int not null auto_increment primary key, dt datetime null default null, description varchar(16));
-- Modify session to allow insertion of zero dates
SET SESSION sql_mode = 'STRICT_ALL_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';
INSERT INTO test_dates (dt, description) values 
                    ('2016-01-02 03:04:05', 'Standard Date'),
                    ('0000-00-00 00:00:00', 'Zero Date'),
                    (null, 'Null Date');

SELECT * FROM test_dates;
SELECT * FROM test_dates WHERE dt is not null;
SELECT * FROM test_dates WHERE dt = 0;
SELECT * FROM test_dates WHERE dt <> 0;

Produces the following, respectively:

Which corresponds to the following using the MySQL client:

mysql> SELECT * FROM test_dates;
+----+---------------------+---------------+
| id | dt                  | description   |
+----+---------------------+---------------+
|  1 | 2016-01-02 03:04:05 | Standard Date |
|  2 | 0000-00-00 00:00:00 | Zero Date     |
|  3 | NULL                | Null Date     |
+----+---------------------+---------------+
3 rows in set (0.00 sec)

mysql> SELECT * FROM test_dates WHERE dt is not null;
+----+---------------------+---------------+
| id | dt                  | description   |
+----+---------------------+---------------+
|  1 | 2016-01-02 03:04:05 | Standard Date |
|  2 | 0000-00-00 00:00:00 | Zero Date     |
+----+---------------------+---------------+
2 rows in set (0.00 sec)

mysql> SELECT * FROM test_dates WHERE dt = 0;
+----+---------------------+-------------+
| id | dt                  | description |
+----+---------------------+-------------+
|  2 | 0000-00-00 00:00:00 | Zero Date   |
+----+---------------------+-------------+
1 row in set (0.00 sec)

mysql> SELECT * FROM test_dates WHERE dt <> 0;
+----+---------------------+---------------+
| id | dt                  | description   |
+----+---------------------+---------------+
|  1 | 2016-01-02 03:04:05 | Standard Date |
+----+---------------------+---------------+
1 row in set (0.00 sec)

Using where dt <> 0 as your WHERE predicate is the best option to remove 0 dates and null values. (Well, your best option would probably be to avoid using 0 dates, but that's neither here nor there.)

[serge-rider]

Also check #1127 - there were a couple of improvements for zero date/time handling in MySQL.

[codetrash]

the explanation is illuminating..I'll try it