0

I'm trying to query a custom table on a DATETIME field. My timezone is UTC+2 and the dates stored in the database are UTC.

shiftEnd in database:

note that the shifts ends at 08:35 UTC, in my timezone that means this shift ends at 10:35.

enter image description here

My query:

$now = (new \DateTime())->format('c');
//$now holds this value: '2021-08-06T09:40:00+02:00'
//time on my clock is 09:40

$query = "
            SELECT *
            FROM time_sheets
            WHERE shiftEnd < '{$now}'
            ";

        return Craft::$app->db->createCommand($query)->queryAll(PDO::FETCH_OBJ);

** also tried this date format but it doesn't give back a result:(new \DateTime())->format('U');

I would expect that this query would not retrieve the record in the screenshot above. Because the shiftEnd is at 10:35 UTC+2.

What am I doing wrong here? Should I manually adjust the date with -2 hours so it matches the UTC date stored in the database. Or is there a way which I could do this automatically?

Additional information custom table:

Migration

public function createTables()
    {
        $this->createTable('{{%time_sheets}}', [
            'id' => $this->primaryKey(),

            'shiftStart'          => $this->dateTime(),
            'shiftEnd'            => $this->dateTime(),
     
            'dateCreated' => $this->dateTime()->notNull(),
            'dateUpdated' => $this->dateTime()->notNull(),
            'uid'         => $this->uid(),
        ]);
    }

Record

<?php


namespace modules\timesheets\records;


use craft\db\ActiveRecord;

class TimeSheetRecord extends ActiveRecord
{





    public static function tableName()
    {
        return '{{%time_sheets}}';
    }




}

Model

<?php

namespace modules\timesheets\models;
use craft\base\Model;


class TimeSheetModel extends Model
{
    
    public function init()
    {
        parent::init();
    }




    public function rules()
    {
        return [
            ['someAttribute', 'string'],
            ['someAttribute', 'default', 'value' => 'Some Default'],
        ];
    }
}

Thanks in advance!

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.