I have a tables record of type timestamp. How do I make my insert statement use the correct time? According to http://www.mysqltutorial.org/mysql-timestamp.aspx
I gather I first must select my time zone
SET time_zone =‘+08:00’;
then how would I make it use the current time?
By default, the current time zone for each connection is the server’s time.
TIMESTAMP is UTC, your insert uses the correct time, the server time to UTC.
The time zone can be set on a per-connection basis.
mysql> SET time_zone = timezone;
The current session time zone setting affects display and storage of time values that are zone-sensitive. This includes the values displayed by functions such as NOW() or CURTIME(), and values stored in and retrieved from TIMESTAMP columns. Values for TIMESTAMP columns are converted from the current time zone to UTC for storage, and from UTC to the current time zone for retrieval.
If you do
SET time_zone ='+08:00';
then it won’t use the current time, it will convert and insert to UTC +8 hours from the current time. Is that what you’re looking for?
no, I want the current time (so no of the +8 stuff)
I live in San Diego, how do I find out what my servers time zone is?
I looked at the DATETIME type also and it may be better
I can use php to get the current date and time using
$date = date('Y-m-d H:i:s');
Then use that variable in my insert statement right?
In the table, do I use the DATETIME datatype?
I’m a little lost here. Your server time is taken from the local regional settings of the server. Using timestamp the current time is converted to UTC, IT’S NOT SHIFTED TO UTC.
What this means, is that if one user inserts something at 14:00, another user, one hour apart, will see it as his hour, 15:00. Which is great, the second user doesn’t have to make any time zone calculation to find out when, in his time, the insert took place.
though, this is when you are are adding a new column, with current date, i dont know if it help.
-- Add Column with Default Current Date Time
ALTER TABLE TestTable
ADD DateInserted DATETIME NOT NULL DEFAULT (GETDATE());
-- Select from table
I read this today.
prolly will not help, lostty84, because luke is using mysql
This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.