Get entry from the database with the correct time using axios

Question

I'm having trouble displaying the correct time. With the UTC default setting in config/app.php, I have a 4 hour difference. When I set the local time zone, I have 2 hours difference. I assume the problem occurs because my database is set to CEST time zone.

mysql> SELECT  @@system_time_zone;
+--------------------+
| @@system_time_zone |
+--------------------+
| CEST               |
+--------------------+
1 row in set (0.00 sec)

The page I am working on will be used locally, which is why I would like to have the same time in the database as displayed on the page. The time is correctly saved in the database, only the displayed time is changed.

DB -> Laravel

2020-04-21 16:55:08 -> 2020-04-21T14:55:08.000000Z

I wonder if manual modification is a good approach. In this case, I can't change the time zone in the database on the server. Is there any solution or workaround?

UPDATE #1

This solution works correctly if I'm fetch time to blade file.

public function getCreatedAtAttribute($value) {
    return \Carbon\Carbon::createFromFormat('Y-m-d H:i:s', $value, 'Europe/Warsaw');
}

When I get an entry using axios createFromFormat does not accept $value without processing. Unfortunately, this solution does not display the correct time.

public function getCreatedAtAttribute($value) {
    $data = \Carbon\Carbon::parse($value);
    return $data::createFromFormat('Y-m-d H:i:s', $data, 'Europe/Warsaw');
}

UPDATE #2

I noticed that after setting the local time zone in config/app.php the time is displayed correctly in blade.php files files without any accessors. Unfortunately, when I download entries using axios Laravel returns the wrong time despite the fact that it is saved correctly in the database. The following accessor works fine.

Accessor method:

public function getCreatedAtAttribute($value) {
    $data = \Carbon\Carbon::parse($value);
    return $data->timezone('Europe/Warsaw');
}

I would like to fetch the time exactly as it is in the database. Otherwise I will have to add accessors to each model.

Please check this link: https://stackoverflow.com/questions/32884388/change-timezone-in-lumen-or-laravel-5 — Nirav Bhoi, Apr 22 '20 at 13:03
`SHOW VARIABLES LIKE '%zone%';` -- There will be 2 entries; both are relevant. — Rick James, Apr 26 '20 at 18:34
What laravel version are you using? You can update the question tag with the version you're using. — Oluwatobi Samuel Omisakin, Apr 27 '20 at 09:09
I'm using Laravel Framework 7.8.1. I have not seen such a tag is available on the list. — neuroine, Apr 27 '20 at 09:16
Actually, I realised these two is the same `2020-04-21 16:55:08 -> 2020-04-21T14:55:08.000000Z`. Do you still have problems with the timezone or the format of this string? — Oluwatobi Samuel Omisakin, Apr 27 '20 at 10:36
Yes, I need to have the same date format in both cases. When I display the date in blade and when I retrieve date using axios. — neuroine, Apr 27 '20 at 13:09
What data type is this stored as in the MySQL database? Please read [these docs](https://dev.mysql.com/doc/refman/8.0/en/datetime.html), and see [*Should I use the datetime or timestamp data type in MySQL?*](https://stackoverflow.com/q/409286/634824), as well as [this blog post](https://www.eversql.com/mysql-datetime-vs-timestamp-column-types-which-one-i-should-use/) — Matt Johnson-Pint, Apr 27 '20 at 15:56

score 3 · Answer 1 · answered Apr 30 '20 at 18:00

The easiest and straight forward way to do it is to update each of your model to use Cast in this way by setting $timestamps attribute to false

//Ninja Model

public $timestamps = false;

This means Laravel will not automatically cast created_at and updated_at attributes to a Carbon instance and you can retrieve your values as you get it from the database.

Other than this, if you need to set a system wide timezone for Carbon/Date time generally i.e disregarding what is set by laravel in app.timezone config path. Then you may need to use:date_default_timezone_set('Europe/Warsaw') in the boot() method of your AppServiceProvider class.

Normally, whatever value you set as the timezone value in app config file should be sufficient to enforce app-wide timezone.

score 2 · Answer 2 · answered Apr 22 '20 at 13:09

You can set timezone for display purpose with Carbon in laravel:

Carbon::createFromFormat($format, $time, $tz);

You can set your database timezone on above carbon function and display it.

Carbon::createFromFormat('Y-m-d H', '1975-05-21 22', 'CEST');

This way you can display proper time as per your timzone.

score 0 · Answer 3 · answered Apr 22 '20 at 13:23

0

You can setup the timezone in your config/app.php to your required timezone - and use DATETIME instead of TIMESTAMP in your migrations to prevent MYSQL automatic conversion to CEST timezone

answered Apr 22 '20 at 13:23

Christophe Hubert

2,833
1
12
25

score 0 · Answer 4 · answered Apr 30 '20 at 10:31

You can configure your time zone in config/app.php by adding the following code

date_default_timezone_set("Europe/Warsaw");

and you can add a column in your table to store time- add column add_time and store value time() in the column on entry of data.

You can add a column which stores time of entry by using following code

alter table your_table name add_column add_date varchar(10) after another_col_name

the data will look like

$mysql_returned = array(

    'data1' => 'somevalue1'
    'data2' => 'somevalue2',
    'data3' => 'somevalue3',
    'data4' => 'somevalue4',
    'add_date' => '1588242603'

);

$time_var = $mysql_returned['add_date'];

then you can convert back into date time by using the following

$your_real_time = date('d-m-Y h:i:s', $time_var);

Get entry from the database with the correct time using axios

4 Answers4