CodeIgniter Forums

Full Version: Save timestamp in database
You're currently viewing a stripped down version of our content. View the full version with proper formatting.
Hi
I can save current timestamp with UNIX_TIMESTAMP function in mysql example 1525352382
My project is international and for each country,i have to show time according timezone it
In php i can set timezone to current country and get time from timestamp
But it is important that what is mysql timezone for base?
Example is it London or other?
Here is a Mind Blowing article on it.

Today I learned about MySQL and timezones

Hope that helps.
(05-03-2018, 09:00 AM)InsiteFX Wrote: [ -> ]Here is a Mind Blowing article on it.

Today I learned about MySQL and timezones

Hope that helps.

The best site i'v ever seen in all site
Thank you
And new problem is
I need timestamp in database when user add data to database and show it in user's app according relate time zone
I save UNIX_TIMESTAMP in database with time zone +00:00 and use output in app with different time zone
Is it right?
Code:
# user created timestamp
`created_at` timestamp default current_timestamp,

# user add or update timestamp
`updated_at` timestamp default current_timestamp on update current_timestamp,

# do not forget that you can add timestamp(6) etc.

For the timezone I found this article.

How to Detect User Timezone in PHP

Let me know if that works for you.
(05-03-2018, 11:12 AM)InsiteFX Wrote: [ -> ]
Code:
# user created timestamp
`created_at` timestamp default current_timestamp,

# user add or update timestamp
`updated_at` timestamp default current_timestamp on update current_timestamp,

# do not forget that you can add timestamp(6) etc.

For the timezone I found this article.

How to Detect User Timezone in PHP

Let me know if that works for you.

In your way,output is example 2018-2-2 12:10:20
But i save unix timestamp because need convert timestamp to user timezone
MySQL by default will use the system time zone internally but it's possible to define a different time zone
for the MySQL server globally or even per transaction. When you insert a date, it is not possible to define
the time zone along with the date string in MySQL. A date like '2013-11-10 00:00' refers to a point in time
since the epoch. You store it and you know what time zone your server is in. Then when you retrieve it, you
can make the necessary adjustments to transform that date form the server's time zone to the client's.

SEE: This MySQL Cheat Sheet

MySQL Timezone Cheatsheet
(05-04-2018, 03:58 AM)InsiteFX Wrote: [ -> ]MySQL by default will use the system time zone internally but it's possible to define a different time zone
for the MySQL server globally or even per transaction. When you insert a date, it is not possible to define
the time zone along with the date string in MySQL. A date like '2013-11-10 00:00' refers to a point in time
since the epoch. You store it and you know what time zone your server is in. Then when you retrieve it, you
can make the necessary adjustments to transform that date form the server's time zone to the client's.

SEE: This MySQL Cheat Sheet

MySQL Timezone Cheatsheet

Thank you Heart