date_default_timezone_set utc(6)
new PDO("mysql:host=localhost;dbname=nametable", "username", "password", );
These dates need to be compared in MySQL using the NOW() function to return the difference in hours, like so:
SELECT TIMESTAMPDIFF ( hour , NOW (), finalize_at ) FROM plans ;But the problem is that the PHP date("Ymd H:i:s") function date uses the PHP time zone setting, and the NOW() function takes MySQL time from the MySQL server.
I am trying to solve this:
- date_default_timezone_set("Europe/Paris"); It only works for PHP.
- date.timezone="Europe/Paris"; It only works for PHP.
- SELECT CONVERT_TZ(now(), "GMT", "MET"); This returns empty.
- mysql> SET time_zone = "Europe/Paris"; This throws an error from the MySQL console.
And the time zone for MySQL does not change.
Is there a way to change the timezone for PHP and MySQL without having to do it from the MySQL console, or set the timezone change somewhere in php.ini and make those values available to both PHP and MySQL.
I really appreciate your support.
You can do this easily with just two lines of PHP.
$tz = (new DateTime("now" , new DateTimeZone("Asia/Kabul" )))-> format("P" ); $pdo -> exec("SET time_zone="$tz";" );For PHP use this function:
date_default_timezone_set() default-time-zone="timezone"If you have the privilege root, you can set the value of the global clock server at runtime with this statement:
SET GLOBAL time_zone = timezone ;Timezone per connection. Each client that connects has its own time zone, given by the time_zone variable . The session variable initially takes its value from the global time_zone variable, but the client can change its own timezone with this statement:
SET timezone = timezone ;Best method Set timezone in PDO MySQL:
If necessary, by mistake you can use: date("P") Example:
new PDO( "mysql:host=localhost;dbname=nametable", "username" , "password" , [ PDO :: MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8;SET time_zone = "". date("P" ). """ ]);I can change my default mysql timezone from the variables section by editing the line that says "timezone" in phpmyadmin.
Here you can also change the format and more you can find in mysql support http://dev.mysql.com/doc/refman/5.7/en/time-zone-support.html I hope it helps you .
$unsafe_variable = (is_numeric($_POST["user_input"]) ? $_POST["user_input"] : "");
And it's much better to use these functions to validate mysql_real_escape_string input.
Setting the time zone (default) is very important for proper data processing. This article "Setting Timezone in php.ini" will help you learn how you can set the timezone in the PHP configuration file (php.ini) or inside any PHP script for temporary use.
Timezone setting in php.ini
To set the default timezone for your PHP environment, follow these 4 easy steps.
1. You need to find the php.ini configuration file and this can be done:
# find / -type f -name php.ini
By default, php.ini is in:
For CentOS, RedHat or Fedora will be:
/etc/php.ini
For Ubuntu, Debian or LinuxMint it will be:
/etc/php5/apache2/php.ini
2. The next step is to search for time zones (for your location), for this, you should visit the site and find your zone for php:
3. We register the found zone in the php.ini file. For example, I will take my zone - 'Europe/Kiev'. Open the configuration file and make changes:
[...] date.timezone = "Europe/Kiev" [...]
ATTENTION! Look for the "date.timezone" option in this file, perhaps this parameter is already registered.
4. Restart the web server:
# service httpd restart
# service apache2 restart
If another server (nginx, tomcat, lighttpd), then restart it.
Also, you can set this parameter in the php file itself as follows:
The Timezone setup in php.ini is now complete.
This is a short article on how to work with timezones when storing date/time values in a database. The databases or programming language that you decide to use is not critical, since the phenomena described in the article are universal. I will show my actions using the MySQL and PHP bundle as an example. The problem with time zones is rather banal. Many often forget about their existence, and some consider them to be something overly complicated. Usually, when setting up a database or when using a web hosting service, your time zones are configured by default. The default settings are fine if you're running a single server, but what happens if you decide to move it?
The same question is logical if you have multiple servers in different locations.
Instead of storing timezones with each date, it's better to start with standard time and associate all your dates with it. This method allows you to make a transition to standard time before the value is stored in the database. In general, it's a good idea to just use UTC +00:00.
What is UTC
The UTC format stands for Coordinated Universal Time and is the successor to Greenwich Mean Time (GMT). The two formats are almost identical, but UTC is considered the accepted standard. In general, UTC is based on 40 different time zones. It is customary to take 00:00 hours as the reference point in UTC. In total, there are 12 main time zones, both with positive and negative values, as well as some additional time zones, like half-hours UTC +04:30. The idea is that if you have a time zone UTC-05:00, then you have to subtract 5 hours from the initial UTC +00:00 to achieve the desired match. That is, if it is 2:00 pm (or 2 pm) and your timezone is UTC-05:00(EST or Eastern Time Zone), then UTC +00:00 will be equal to 19:00 (or 7 pm).- Graphical representation of world time zones (Wikipedia)
- List of time zone abbreviations (Wikipedia)
Store time in UTC +00:00 format
The first thing we need to do is decide which timezone to use to store all of our values. I highly recommend taking UTC +00:00- for no particular reason other than that it's easy to remember and 0 is the ideal reference point. Since we know that all dates are in UTC +00:00, it becomes much easier to represent the proper time for each user based on their personal time zones. Once the user has chosen (or we have auto-detect) their time zone, outputting the appropriate time for that user becomes commonplace.Setting the default time zone
Assuming all of our dates/times are stored in UTC, we can simply set the default time zone to be used to display each time. This will automatically pick up the date and time for a specific user. In PHP, all we need to do is set the time zone, like so:date_default_timezone_set("EST");
We can do this in some initialization block at the top of our code and we're done: all dates will be displayed properly for the time zone the user is in.
If we want to have full control over the date and time, we can build a function like this:
function get_date($date) ( date_default_timezone_set("EST"); $date = date("Y-m-d H:i:s", strtotime($date)); date_default_timezone_set("UTC"); return $date; )
Setting PHP to UTC +00:00
Of course, things wouldn't be so easy if we didn't set our system to UTC as the default time zone. I'm sure it can be done in much the same way in other languages, but in PHP it's done like this:date.timezone = UTC
Now whenever we use things like for example the function time(), this automatically gives us the UTC time with the appropriate offset based on our server time. If we want to set up dates in a database like MySQL, we do this:
date("Y-m-d H:i:s", time());
If we didn't have access to change the PHP configuration, then we have to set the time manually when setting the date, like this:
date("Y-m-d H:i:s", time() - date("Z"));
Setting MySQL to UTC +00:00
Setting the date directly, as we did above, is applicable if we do not have access to change MySQL configuration parameters. However, if we want to use TIMESTAMP, which is usually self-updating, or a date function from MySQL like NOW(), then we must configure MySQL to use UTC as the default time zone.In MySQL this is done by setting the UTC offset, which can be done just as easily as it was in PHP by making the following changes:
default-time-zone = "+00:00"
Update existing dates/times
If you decide to standardize time zones using UTC, where the default time zone is UTC +00:00 offset, then your current time in your database will now be invalid.Luckily, MySQL provides a handy feature that allows us to easily update existing time zones.
NOTE: If you have any timestamps, then they need to be updated first.
We can search all date and time fields in our database with the following query:
SELECT * FROM `information_schema`.`COLUMNS` WHERE `TABLE_SCHEMA`="table_name" AND (`DATA_TYPE`="timestamp" OR `DATA_TYPE`="datetime" OR `DATA_TYPE`="date");
From here, we can use the following query to update the dates/times in our tables:
UPDATE `table_name` SET `timestamp`=CONVERT_TZ(`timestamp`, "-05:00", "+00:00");
Where -05:00
- the current time zone in which the time is stored (as in the previous case, it refers to Eastern). time zone +00:00
, which we want to convert to, in this case refers to UTC.
If during the process of managing virtual hosting it became necessary to change the default time zone, then this action can be easily performed in a few steps, which are described below.
Change timezone via Select PHP Version
To change the time zone go to cPanel -> Software -> Select PHP Version -> Switch to PHP options.
The next step is to change the value of the variable date.timezone to the time zone you need. You can find the list of available time zones in the last paragraph of the article.
After making the necessary changes, it is important to click on the button "Save Changes" located at the bottom of the page PHP Selector.
To ensure that the specified timezone value has been applied, you can use the PHP function phpinfo(), which displays information about all set php options. To do this, create public_html click on the button "File" in the controller menu.
- In field New filename indicate, for example, phpinfo.php then press the button Create New File.
- As a result, the file with the specified name will appear in the list of others on the hosting.
- Next, you need to open this file for editing. To do this, left-click on the desired file, then in the dispatcher menu, click on the button Change.
- In the form that appears, leave all the default parameters and click on the button Edit, to open a text editor.
- In the window that opens, enter the following code:
Then save the file and open it in a browser. In this example, the file will be available at:
http://main_domain/phpinfo.php
In the page that opens, find the section date, which will contain a table with the value of the current time zone. In this case, the above area is displayed Europe/Kiev.
This completes the process of changing the timezone.
List of timezones to set timezone
In addition, here is a list of possible time zone values:
Africa/Cairo Africa/Casablanca Africa/Harare Africa/Monrovia Africa/Nairobi America/Bogota America/Buenos_Aires America/Caracas America/Chihuahua America/La_Paz America/Lima America/Mazatlan America/Mexico_City America/Monterrey America/Santiago America/Tijuana Asia/ Almaty Asia/Baghdad Asia/Baku Asia/Bangkok Asia/Chongqing Asia/Dhaka Asia/Hong_Kong Asia/Irkutsk Asia/Jakarta Asia/Jerusalem Asia/Kabul Asia/Karachi Asia/Kathmandu Asia/Kolkata Asia/Krasnoyarsk Asia/Kuala_Lumpur Asia/Kuwait Asia /Magadan Asia/Muscat Asia/Novosibirsk Asia/Riyadh Asia/Seoul Asia/Singapore Asia/Taipei Asia/Tashkent Asia/Tbilisi Asia/Tehran Asia/Tokyo Asia/Ulaanbaatar Asia/Urumqi Asia/Vladivostok Asia/Yakutsk Asia/Yekaterinburg Asia/Yerevan Atlantic/Azores Atlantic/Cape_Verde Atlantic/Stanley Australia/Adelaide Australia/Brisbane Australia/Canberra Australia/Darwin Australia/Hobart Australia/Melbourne Australia/Perth Australia/Sydney Canada/Atlantic Canada/Newfoundland Canada/Saskatchewan Europe/Amsterdam Europe /Athens Europe/Belgrade Europe/Berlin Europe/Bratislava Europe/Brussels Europe/Bucharest Europe/Budapest Europe/Copenhagen Europe/Dublin Europe/Helsinki Europe/Istanbul Europe/Kiev Europe/Lisbon Europe/Ljubljana Europe/London Europe/Madrid Europe/Minsk Europe/Moscow Europe/Paris Europe/Prague Europe/Riga Europe/Rome Europe/Sarajevo Europe/Skopje Europe/Sofia Europe/Stockholm Europe/Tallinn Europe/Vienna Europe/Vilnius Europe/Volgograd Europe/Warsaw Europe/Zagreb Greenland Pacific/Auckland Pacific /Fiji Pacific/Guam Pacific/Midway Pacific/Port_Moresby US/Alaska US/Arizona US/Central US/Eastern US/East-Indiana US/Hawaii US/Mountain US/Pacific US/Samoa
Want a new website name? We have available
belts automatic (6)
I need to know what timezone my users are in based on their IP or HTTP header.
I got a lot of answers to this question, but I couldn't understand the answer. Some say using -new Date().getTimezoneOffset()/60(). But what does it mean?
I have date_default_timezone_set("Asia/Calcutta"); at the root of my page (index.php). So for this I have to dynamically set the time zone and set it instead of Asia/Calcutta .
Answers
The browser's timezone information is not part of the HTTP specification, so you can't just get it from the header.
If you have location coordinates (for example, from a mobile GPS device), you can find the time zone using one of these methods. However, geolocation by IP address is not a great solution, because often the IP address of the provider or proxy server, which may be in a different time zone.
There are some strategies you can use to try and determine the time zone, such as using the jsTimeZoneDetect library, which is a great starting point but not perfect enough that you can't just rely on it alone. If you're using moment.js, moment-time has a built-in function called moment.tz.guess() that does the exact same thing.
The idea of using JavaScript's getTimezoneOffset() function is wrong in that you don't get the time zone - just one offset for a particular date. See the wiki section of the TimeZone tag topic titled "TimeZone! = Offset".
However you look at it, ultimately you need to decide one of two approaches:
- Ask the user to tell you their timezone, from either a dropdown list or a map-based time zone control.
- Send the time to the browser in UTC only, and use JavaScript in the browser to convert to whatever local time zone the user can set their computer to.
I discuss this in more detail (in terms of AC#) in this answer.
The time zone is not available in the HTTP header, but the country (abbreviation) is in the ACCEPT_LANGUAGE header. It will be something like "en-US" (US is the country code). This can be combined with JavaScript information to get an idea of the user's time zone.
This is what I use in JS:
Function timezone() ( var now = new Date(); var jano = new Date(now.getFullYear(), 0, 1).getTimezoneOffset()/-60; var julo = new Date(now.getFullYear(), 6 , 1).getTimezoneOffset()/-60; var tz = Math.min(jano, julo); if (jano != julo) tz += ((jano< julo) ? "S" : "W") + Math.abs(jano - julo); return tz; }
This returns a string like "-6S1" for the central zone (standard time offset is -6 hours, daylight savings time is in effect in the summer and adds 1 hour). I am using a cookie to make this available to PHP. PHP searches the TZ database for zones that match this and countries. For here (US, -6S1) there are 7 corresponding zones, the first is "America/Chicago".
By the way, there are two zones in the database where DST adds anything other than 1 hour: Lord Howe Island (10.5W0.5) and Troll Station, Antarctica (0W2).
One of them is to ask them! Especially in those member systems where you can capture/register a user - give them a choice at that point. Simple but accurate.
This works great...
echo<<
To summarize Matt Johnson's answer in terms of code:
Not that this is a recommendation, the more there was a division of the paradigm, but most aggressive The way to handle timezone information in a web application (which is not exclusive to ASP.NET MVC) was as follows:
All times on the server are UTC. This means using, as you said, DateTime.UtcNow .
Try not to trust the client by passing dates to the server as little as possible. For example, if you need "now", don't create a date on the client and then pass it to the server. Either create the date in your GET and pass it to the ViewModel or POST do DateTime.UtcNow .
Pretty standard fare so far, but this is where things get "interesting".
If you need to accept a date from a client, use javascript to make sure the data you send to the server is in UTC. The client knows what time zone it is in, so it can convert the time to UTC with reasonable accuracy.
When rendering views, they used an HTML5 element
They will then use javascript to convert the UTC time to the client's local time. The script will find all elements
This way, they never had to track, store, or manage the time zone of the clients. The server didn't care what timezone the client was in and didn't need to do any timezone translations. It just spit out UTC and let the client convert it to something reasonable. This is easy from the browser because it knows what timezone it is in. If the client has changed its timezone, the web application will automatically update itself. The only thing they kept was the date format string for the user's locale.
I'm not saying this is the best approach, but it was a completely different thing that I hadn't seen before. You might get interesting ideas from it.
