Issue Bug Closed

#96890Wrong processing and display of native datetime(dbType) fields due to server timezone

Imko Schumacher 02/14/22, 20:01 updated 12/02/25, 14:33

Description

Wrong processing and display of native datetime(dbType) fields due to server timezone

Inside DatabaseRowDateTimeFields class native datetime (dbType) values are treated as UTC values, whereas the rest uses the local timezone (e.g. BackendUtility::datetime).
This causes the TCA title (bug 1) and readOnly fields (bug 2) to be formatted wrong (with an time offset).
The conversion also breaks the title for time fields (bug 3) completely.

Prerequisite

  • Timezone != ‘UTC’, e.g. timezone: America/New_York (.ddev/config.yaml)
  • Specified configuration (see below)

Steps to reproduce

Bug 1

  1. Create a new DateTime title error record and set mydatetime to 12:00 20-02-2022
  2. Save record
  3. Look at the record title
    • Actual value (wrong time): Edit DateTime title error "20-02-22 07:00" on page "recordStorage"
    • Expected value: Edit DateTime title error "20-02-22 12:00" on page "recordStorage"
      dateTimeTitle-saved.png

Bug 2

  1. Uncomment readOnly in TCA and clear cache/reload
  2. Open record from bug 1
  3. Look at the disable field (mydatetime)
    • Actual value (wrong time): 20-02-22 07:00
    • Expected value: 20-02-22 12:00
      dateTimeTitle-readOnly.png

Bug 3

  1. Change TCA config for bug 3 and clear cache/reload
  2. Create a new DateTime title error record and set mytime to 12:00
  3. Save record
    • Actual value: 2022-02-12T07:00:00-05:00
    • Expected value: 12:00
      dateTimeTitle-time.png

Configuration

TCA:

return [
    'ctrl' => [
        'title' => 'DateTime title error',
        'label' => 'mydatetime', // Bug 1 and 2
        // 'label' => 'time', // Bug 3
    ],
    'columns' => [
        // Bug 1 and 2
        'mydatetime' => [
            'label' => 'mydatetime',
            'config' => [
                'type' => 'input',
                'renderType' => 'inputDateTime',
                'dbType' => 'datetime',
                'eval' => 'datetime,null',
                // 'readOnly' => true, // Bug 2
            ],
        ],
        // Bug 3
        'mytime' => [
            'label' => 'mytime',
            'config' => [
                'type' => 'input',
                'renderType' => 'inputDateTime',
                'dbType' => 'time',
                'eval' => 'time,null',
            ],
        ],
    ],
    'types' => [
        '0' => ['showitem' => 'mydatetime,mytime'],
    ],
];

SQL:

mydatetime datetime default NULL,
mytime time default NULL,

Source of the problem

Classes using this value (as far as I know)

  • Bug 1: TcaRecordTitle:320,360
    • Example dataflow
      • Database: 2022-02-22 12:00
      • DatabaseRowDateTimeFields: generates a string like 2022-02-22T07:00:00-05:00 (UTC)
      • TcaRecordTitle: converted to a timestamp by strtotime to 1645531200
      • TcaRecordTitle: converted by BackendUtility::datetime (with date function, formats in server timezone) to 2022-02-22 07:00
  • Bug 2: InputDateTimeElement:114
  • Bug 3: TcaRecordTitle???
  • Custom functions using formattedLabel_userFunc

Reviews 2