For a date in far ago years, Metabase displays the timestamp in strange ways

1

We got a minor question when looking at a production Metabase v0.32, located in MST (Mountain Standard Time).

We tried with data sources of MariaDB and SQL Server (MSSQL), and the system behaviors are the same. So, we guess, the reason comes from Metabase.

  • In a native query, any DataTime type variable dated before 1900-01-01 will display a value with T23:26:08.000-07:33:52 of the day before. For example, 1899-12-31 will show 1899-12-30T23:26:08.000-07:33:52.

    However, if we give a specific format to the DateTime variable, e.g., in MariaDB, the below expression will display the expected value 1899-12-31 00:00:00.000000.

    ...
date_format(tab_date.value , '%Y-%m-%d %H:%i:%s.%f') as `value_formated`
...

  • Moreover, between 1900-01-01 and 1906-08-31, inclusive, the date will display T00:00:00.000-07:33:52 by default. We suspect the time difference -07:33:52 might be related to our location in MST because this time zone is 7 hours behind UTC, and 6 hours behind during daylight saving time.

  • We also noticed other funning things, e.g., 1906-09-01, and it displays as September 1, 1906, 12:33 AM, so the time of the day is 12:33 AM, not 12:00 AM. And, if necessary, we can test more to figure out the exact boundaries.

  • Finally, we have start having the intuitively expected system behavior from 1906-09-02, and it displays September 2, 1906, 12:00 AM.

Similarly, MSSQL shows the same behavior in our testing, therefore, we believe the reason is in Metabase.

Our Questions:

  • We wonder why the time of the day got shifted in the display.
  • The Metabase native query goes through 1) the Java binary of Metabase, 2) the PostgreSQL application database (metadata) of Metabase, and 3) the data source, e.g., MariaDB or MSSQL, etc. So, where does the result get affected in timezone related conversions?

We are new to this area, so we highly appreciate any hints and suggestions.

More Details:

If necessary, we hope the following details can help reproduce our testing results. Again, we appreciate your patience and help.

  1. Query to a database of MariaDB 10.1.9:

    image
    image591×597 22.9 KB

    Source code:

    select
  version() as `database`
  , tab_date.value as `value`
  , date_format(tab_date.value , '%Y-%m-%d %H:%i:%s.%f') as `value_formated`
from
 (
    select convert('1899-12-31 00:00:00.000000', datetime) as `value`
 ) as tab_date

union all

select
  version() as `database`
  , tab_date.value as `value`
  , date_format(tab_date.value , '%Y-%m-%d %H:%i:%s.%f') as `value_formated`
from
 (
    select convert('1900-01-01 00:00:00.000000', datetime) as `value`
 ) as tab_date

union all

select
  version() as `database`
  , tab_date.value as `value`
  , date_format(tab_date.value , '%Y-%m-%d %H:%i:%s.%f') as `value_formated`
from
 (
    select convert('1906-08-31 00:00:00.000000', datetime) as `value`
 ) as tab_date

union all

select
  version() as `database`
  , tab_date.value as `value`
  , date_format(tab_date.value , '%Y-%m-%d %H:%i:%s.%f') as `value_formated`
from
 (
    select convert('1906-09-01 00:00:00.000000', datetime) as `value`
 ) as tab_date

union all

select
  version() as `database`
  , tab_date.value as `value`
  , date_format(tab_date.value , '%Y-%m-%d %H:%i:%s.%f') as `value_formated`
from
 (
    select convert('1906-09-02 00:00:00.000000', datetime) as `value`
 ) as tab_date

union all

select
  version() as `database`
  , tab_date.value as `value`
  , date_format(tab_date.value , '%Y-%m-%d %H:%i:%s.%f') as `value_formated`
from
 (
    select convert('1906-10-01 00:00:00.000000', datetime) as `value`
 ) as tab_date

union all

select
  version() as `database`
  , tab_date.value as `value`
  , date_format(tab_date.value , '%Y-%m-%d %H:%i:%s.%f') as `value_formated`
from
 (
    select convert('1907-01-01 00:00:00.000000', datetime) as `value`
 ) as tab_date
;

  2. Query to a database of Microsoft SQL Server 2016:

    image
    image785×586 30.9 KB

    Source code:

    select
  left(@@version, 50) as [database]
  , tab_date.value as [value]
  , format(tab_date.value , 'yyyy-MM-dd HH:mm:ss.ffffff') as [value_formated]
from
 (
    select convert(datetime, '1899-12-31') as [value]
 ) as tab_date

union all

select
  left(@@version, 50) as [database]
  , tab_date.value as [value]
  , format(tab_date.value , 'yyyy-MM-dd HH:mm:ss.ffffff') as [value_formated]
from
 (
    select convert(datetime, '1900-01-01') as [value]
 ) as tab_date

union all

select
  left(@@version, 50) as [database]
  , tab_date.value as [value]
  , format(tab_date.value , 'yyyy-MM-dd HH:mm:ss.ffffff') as [value_formated]
from
 (
    select convert(datetime, '1906-08-31') as [value]
 ) as tab_date

union all

select
  left(@@version, 50) as [database]
  , tab_date.value as [value]
  , format(tab_date.value , 'yyyy-MM-dd HH:mm:ss.ffffff') as [value_formated]
from
 (
    select convert(datetime, '1906-09-01') as [value]
 ) as tab_date

union all

select
  left(@@version, 50) as [database]
  , tab_date.value as [value]
  , format(tab_date.value , 'yyyy-MM-dd HH:mm:ss.ffffff') as [value_formated]
from
 (
    select convert(datetime, '1906-09-02') as [value]
 ) as tab_date

union all

select
  left(@@version, 50) as [database]
  , tab_date.value as [value]
  , format(tab_date.value , 'yyyy-MM-dd HH:mm:ss.ffffff') as [value_formated]
from
 (
    select convert(datetime, '1906-10-01') as [value]
 ) as tab_date

union all

select
  left(@@version, 50) as [database]
  , tab_date.value as [value]
  , format(tab_date.value , 'yyyy-MM-dd HH:mm:ss.ffffff') as [value_formated]
from
 (
    select convert(datetime, '1907-01-01') as [value]
 ) as tab_date
;