php datetime format mysql
PHP date() format when inserting into datetime in MySQL
What is the correct format to pass to the date() function in PHP if I want to insert the result into a MySQL datetime type column?
I’ve been trying date(‘Y-M-D G:i:s’) but that just inserts «0000-00-00 00:00:00» everytime.
13 Answers 13
Try: date(‘Y-m-d H:i:s’) which uses the numeric equivalents.
From the comments of php’s date() manual page:
Here’s an alternative solution: if you have the date in PHP as a timestamp, bypass handling it with PHP and let the DB take care of transforming it by using the FROM_UNIXTIME function.
I use the following PHP code to create a variable that I insert into a MySQL DATETIME column.
This will hold the server’s current Date and Time.
I use this function (PHP 7)
Older versions of PHP (PHP
Format time stamp to MySQL DATETIME column :
Format MySQL datetime with PHP
There is no need no use the date() method from PHP if you don’t use a timestamp. If dateposted is a datetime column, you can insert the current date like this:
This has been driving me mad looking for a simple answer. Finally I made this function that seems to catch all input and give a good SQL string that is correct or at least valid and checkable. If it’s 1999-12-31 it’s probably wrong but won’t throw a bad error in MySQL.
Note: I understood the question referring to current time.
Php datetime format mysql
This section describes the functions that can be used to manipulate temporal values. See Section 11.2, “Date and Time Data Types”, for a description of the range of values each date and time type has and the valid formats in which values may be specified.
Table 12.11 Date and Time Functions
Here is an example that uses date functions. The following query selects all rows with a date_col value from within the last 30 days:
The query also selects rows with dates that lie in the future.
Functions that expect date values usually accept datetime values and ignore the time part. Functions that expect time values usually accept datetime values and ignore the date part.
Other functions expect complete dates and return NULL for incomplete dates. These include functions that perform date arithmetic or that map parts of dates to names. For example:
ADDTIME() adds expr2 to expr1 and returns the result. expr1 is a time or datetime expression, and expr2 is a time expression.
CONVERT_TZ() converts a datetime value dt from the time zone given by from_tz to the time zone given by to_tz and returns the resulting value. Time zones are specified as described in Section 5.1.15, “MySQL Server Time Zone Support”. This function returns NULL if the arguments are invalid.
If the value falls out of the supported range of the TIMESTAMP type when converted from from_tz to UTC, no conversion occurs. The TIMESTAMP range is described in Section 11.2.1, “Date and Time Data Type Syntax”.
Returns the current date as a value in ‘ YYYY-MM-DD ‘ or YYYYMMDD format, depending on whether the function is used in string or numeric context.
Класс DateTime
(PHP 5 >= 5.2.0, PHP 7, PHP 8)
Введение
Обзор классов
Список изменений
Содержание
User Contributed Notes 26 notes
Set Timezone and formatting.
= time ();
$timeZone = new \ DateTimeZone ( ‘Asia/Tokyo’ );
DateTime supports microseconds since 5.2.2. This is mentioned in the documentation for the date function, but bears repeating here. You can create a DateTime with fractional seconds and retrieve that value using the ‘u’ format string.
// Instantiate a DateTime with microseconds.
$d = new DateTime ( ‘2011-01-01T15:03:01.012345Z’ );
There is a subtle difference between the following two statments which causes JavaScript’s Date object on iPhones to fail.
/**
On my local machine this results in:
Both of these strings are valid ISO8601 datetime strings, but the latter is not accepted by the constructor of JavaScript’s date object on iPhone. (Possibly other browsers as well)
*/
?>
Our solution was to create the following constant on our DateHelper object.
class DateHelper
<
/**
* An ISO8601 format string for PHP’s date functions that’s compatible with JavaScript’s Date’s constructor method
* Example: 2013-04-12T16:40:00-04:00
*
* PHP’s ISO8601 constant doesn’t add the colon to the timezone offset which is required for iPhone
**/
const ISO8601 = ‘Y-m-d\TH:i:sP’ ;
>
?>
Small but powerful extension to DateTime
class Blar_DateTime extends DateTime <
= new Blar_DateTime ( ‘1879-03-14’ );
Albert Einstein would now be 130 years old.
Albert Einstein would now be 130 Years, 10 Months, 10 Days old.
Albert Einstein was on 2010-10-10 131 years old.
Example displaying each time format:
$dateTime = new DateTime();
The above example will output:
At PHP 7.1 the DateTime constructor incorporates microseconds when constructed from the current time. Make your comparisons carefully, since two DateTime objects constructed one after another are now more likely to have different values.
This caused some confusion with a blog I was working on and just wanted to make other people aware of this. If you use createFromFormat to turn a date into a timestamp it will include the current time. For example:
if you’d like to print all the built-in formats,
This might be unexpected behavior:
#or use the interval
#$date1->add(new DateInterval(«P1M»));
#will produce 2017-10-1
#not 2017-09-30
A good way I did to work with millisecond is transforming the time in milliseconds.
function timeToMilliseconds($time) <
$dateTime = new DateTime($time);
If you have timezone information in the time string you construct the DateTime object with, you cannot add an extra timezone in the constructor. It will ignore the timezone information in the time string:
$date = new DateTime(«2010-07-05T06:00:00Z», new DateTimeZone(«Europe/Amsterdam»));
will create a DateTime object set to «2010-07-05 06:00:00+0200» (+2 being the TZ offset for Europe/Amsterdam)
To get this done, you will need to set the timezone separately:
$date = new DateTime(«2010-07-05T06:00:00Z»);
$date->setTimeZone(new DateTimeZone(«Europe/Amsterdam»);
This will create a DateTime object set to «2010-07-05 08:00:00+0200»
It isn’t obvious from the above, but you can insert a letter of the alphabet directly into the date string by escaping it with a backslash in the format string. Note that if you are using «double» speech marks around the format string, you will have to further escape each backslash with another backslash! If you are using ‘single’ speech marks around the format string, then you only need one backslash.
For instance, to create a string like «Y2014M01D29T1633», you *could* use string concatenation like so:
please note that using
setTimezone
setTimestamp
setDate
setTime
etc..
$original = new DateTime(«now»);
so a datetime object is mutable
(Editors note: PHP 5.5 adds DateTimeImmutable which does not modify the original object, instead creating a new instance.)
Create function to convert GregorianDate to JulianDayCount
Note that the ISO8601 constant will not correctly parse all possible ISO8601 compliant formats, as it does not support fractional seconds. If you need to be strictly compliant to that standard you will have to write your own format.
Bug report #51950 has unfortunately be closed as «not a bug» even though it’s a clear violation of the ISO8601 standard.
It seems like, due to changes in the DateTimeZone class in PHP 5.5, when creating a date and specifying the timezone as a a string like ‘EDT’, then getting the timezone from the date object and trying to use that to set the timezone on a date object you will have problems but never know it. Take the following code:
Be aware that DateTime may ignore fractional seconds for some formats, but not when using the ISO 8601 time format, as documented by this bug:
$dateTime = DateTime::createFromFormat(
DateTime::ISO8601,
‘2009-04-16T12:07:23.596Z’
);
// bool(false)
Be aware of this behaviour:
In my opinion, the former date should be adjusted to 2014/11/30, that is, the last day in the previous month.
Here is easiest way to find the days difference between two dates:
If you’re stuck on a PHP 5.1 system (unfortunately one of my clients is on a rather horrible webhost who claims they cannot upgrade php) you can use this as a quick workaround:
If you need DateTime::createFromFormat functionality in versions class DateClass extends DateTime <
$regexpArray [ ‘Y’ ] = «(?P 19|20\d\d)» ;
$regexpArray [ ‘m’ ] = «(?P 08|1[012])» ;
$regexpArray [ ‘d’ ] = «(?P 03|[12]1|3[01])» ;
$regexpArray [ ‘-‘ ] = «[-]» ;
$regexpArray [ ‘.’ ] = «[\. /.]» ;
$regexpArray [ ‘:’ ] = «[:]» ;
$regexpArray [ ‘space’ ] = «[\s]» ;
$regexpArray [ ‘H’ ] = «(?P 02|13|21)» ;
$regexpArray [ ‘i’ ] = «(?P42)» ;
$regexpArray [ ‘s’ ] = «(?P31)» ;
Работа с датой и временем в MySQL+PHP
Ниже представлен пример, в котором используются функции даты. Приведенный запрос выбирает все записи с величиной date_col в течение последних 30 дней:
Примечание: в версии 4.0 функция WEEK(#,0) была изменена с целью соответствия календарю США.
Заметьте, если неделя является последней неделей прошлого года, MySQL вернет 0 если вы не указали 2 или 3 как опциональный аргумент:
Можно считать, что MySQL должен вернуть 52, так как данная дата и является 52-ой неделей года 1999. Мы решили возвращать 0, так как мы хотим, чтобы функция давала «номер недели в указанном году». Это делает функцию WEEK() более надежной при использовании совместно с другими функциями, которые вычисляют части дат.
Если вам все же важно уточнить корректную неделю в году, тогда вы можете использовать 2 или 3 как опциональный аргумент или использовать YEARWEEK()
Обратите внимание, что номер недели отличается от того, который возвращает функция WEEK() (0), будучи вызванной с опциональным аргументом 0 или 1. Это потому, что WEEK() возвращает номер недели именно в указанном году.
HOUR(time)- Возвращает час для аргумента time в диапазоне от 0 до 23:
В MySQL формат выражения expr допускает любые разделительные знаки. Разделители, представленные в данной таблице, приведены в качестве примеров. Если аргумент date является величиной типа DATE и предполагаемые вычисления включают в себя только части YEAR, MONTH, и DAY (т.е. не содержат временной части TIME), то результат представляется величиной типа DATE. В других случаях результат представляет собой величину DATETIME:
При использовании некорректных значений дат результат будет равен NULL. Если при суммировании MONTH, YEAR_MONTH или YEAR номер дня в результирующей дате превышает максимальное количество дней в новом месяце, то номер дня результирующей даты принимается равным последнему дню нового месяца:
Из предыдущего примера видно, что слово INTERVAL и ключевое слово type не являются регистро-зависимыми.
Функция TO_DAYS() не предназначена для использования с величинами, предшествующими введению григорианского календаря (1582), поскольку не учитывает дни, утерянные при изменении календаря.
Функция FROM_DAYS() не предназначена для использования с величинами, предшествующими введению григорианского календаря (1582), поскольку она не учитывает дни, утерянные при изменении календаря.
Все другие символы просто копируются в результирующее выражение без интерпретации:
В MySQL 3.23 символ ‘%’ должен предшествовать символам определителя формата. В более ранних версиях MySQL символ ‘%’ необязателен.
Причина того, что промежутки для месяца и дня начинаются с нуля заключается в том, что MySQL позволяет использовать неполные даты, такие как ‘2004-00-00’, начиная с MySQL 3.23.
При использовании функции UNIX_TIMESTAMP в столбце TIMESTAMP эта функция будет возвращать величину внутренней временной метки непосредственно, без подразумеваемого преобразования строки во временную метку («string-to-unix-timestamp» ). Если заданная дата выходит за пределы допустимого диапазона, то функция UNIX_TIMESTAMP() возвратит 0, но следует учитывать, что выполняется только базовая проверка (год 1970-2037, месяц 01-12, день 01-31). Если необходимо выполнить вычитание столбцов UNIX_TIMESTAMP(), результат можно преобразовать к целым числам со знаком. See Раздел 6.3.5, «Функции приведения типов».
Convert a date format in PHP [duplicate]
I am trying to convert a date from yyyy-mm-dd to dd-mm-yyyy (but not in SQL); however I don’t know how the date function requires a timestamp, and I can’t get a timestamp from this string.
How is this possible?
18 Answers 18
Use strtotime() and date() :
(See the strtotime and date documentation on the PHP site.)
Note that this was a quick solution to the original question. For more extensive conversions, you should really be using the DateTime class to parse and format 🙂
If you’d like to avoid the strtotime conversion (for example, strtotime is not being able to parse your input) you can use,
Or if the source-format always is «Y-m-d» (yyyy-mm-dd), then just use DateTime:
Without the date conversion overhead, I am not sure it’ll matter much.
This code works for every date format.
Also another obscure possibility:
I don’t know if I would use it but still 🙂
Note: Because this post’s answer sometimes gets upvoted, I came back here to kindly ask people not to upvote it anymore. My answer is ancient, not technically correct, and there are several better approaches right here. I’m only keeping it here for historical purposes.
Although the documentation poorly describes the strtotime function, @rjmunro correctly addressed the issue in his comment: it’s in ISO format date «YYYY-MM-DD».
Also, even though my Date_Converter function might still work, I’d like to warn that there may be imprecise statements below, so please do disregard them.
The most voted answer is actually incorrect!
The PHP strtotime manual here states that «The function expects to be given a string containing an English date format». What it actually means is that it expects an American US date format, such as «m-d-Y» or «m/d/Y».
I wrote a little function to return dates in several formats. Use and modify at will. If anyone does turn that into a class, I’d be glad if that would be shared.