php datetime to sql datetime
MSSQL datetime datetime in php
I have a problem where i think exist a couple solutions.
scenario I have build this intranetpage where internal performance KPI’s are visible for the office. The data is extracted from a MSSQL database and shown on the webpage in combination with PHP, than the data and converted to an array so Google Chart API can create beautiful charts from there. So far, so good!
But on some days, no tickets are created like a saterday or sunday. Thats where things go wrong.
Today is Thursday and it shows:
The problem is that there are no records (tickets) with a create-date on saturday or sunday, so MSSQL cant count them.
Possible solution One solution is that somehow MSSQL shows Saterday and Sunday in the result table. But i dont think this is possible, because the records simply dont exist.
Other solution Retrieve the datetime used in the MSSQL query and parse them to my intranetpage so the google charts can display those dates instead of the PHP created dates. But than i get a «Convert datetime class to string» error.
Because the datetime in MSSQL is a datetime type and not a varchar type. After trying a lot of things and searching even more on google and stackoverflow, i can succesfull convert a datetime to varchar. But than the part where i take todays date and go back 7 days, doesnt work anymore.
My PHP (and query) look as follow:
I broke the two pieces of code in half, but actually they are
The table looks like this (at least, the interesting part):
I count the amount of occurrences by date (bb_casestartingtime) as a total. So today i have today 2 records yesterday 1 record
Acutal Question can somebody help me to extraced the date-time which is used in the query?
I only use one table called: FilteredIncident Is there a way to convert the datetime bb_casestartingtime to a varchar (so PHP can extract it) but still count the amount of occurrences per day?
If any information is needed, please ask
(I rewrote my entire question, because i wrote the initial post in a rush)
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.
Класс 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 09|1[012])» ;
$regexpArray [ ‘d’ ] = «(?P 03|[12]2|3[01])» ;
$regexpArray [ ‘-‘ ] = «[-]» ;
$regexpArray [ ‘.’ ] = «[\. /.]» ;
$regexpArray [ ‘:’ ] = «[:]» ;
$regexpArray [ ‘space’ ] = «[\s]» ;
$regexpArray [ ‘H’ ] = «(?P 05|12|22)» ;
$regexpArray [ ‘i’ ] = «(?P17)» ;
$regexpArray [ ‘s’ ] = «(?P59)» ;
How to get time and date from datetime stamp in PHP?
How to achieve that? Can you give me example?
7 Answers 7
edit: To keep the AM/PM format use
strtotime creates a UNIX timestamp from the string you pass to it.
For more information about date() function, plz visit http://php.net/manual/en/function.date.php
This is probably not the cleanest way of doing it, but you can use an explode (note that there is NO validation at all here). It will be faster than a proper date manipulation.
If your version of PHP is new enough, check out date_parse() and the array it returns. You can then format date or time portions using the relevant entries.
This should do the trick:
You could also do something like this but it’s not preferred way:
Yes this is possible, and the perfect example can be found here http://php.net/manual/en/function.date.php
Not the answer you’re looking for? Browse other questions tagged php date or ask your own question.
Linked
Related
Hot Network Questions
Subscribe to RSS
To subscribe to this RSS feed, copy and paste this URL into your RSS reader.
site design / logo © 2021 Stack Exchange Inc; user contributions licensed under cc by-sa. rev 2021.9.17.40238
By clicking “Accept all cookies”, you agree Stack Exchange can store cookies on your device and disclose information in accordance with our Cookie Policy.
Datetime vs Date and Time Mysql
I generally use datetime field to store created_time updated time of data within an application.
But now i have come across a database table where they have kept date and time separate fields in table.
4 Answers 4
There is a huge difference in performance when using DATE field above DATETIME field. I have a table with more then 4.000.000 records and for testing purposes I added 2 fields with both their own index. One using DATETIME and the other field using DATE.
I disabled MySQL query cache to be able to test properly and looped over the same query for 1000x:
SELECT * FROM `logs` WHERE `dt` BETWEEN ‘2015-04-01’ AND ‘2015-05-01’ LIMIT 10000,10; DATETIME INDEX: 197.564 seconds.
SELECT * FROM `logs` WHERE `d` BETWEEN ‘2015-04-01’ AND ‘2015-05-01’ LIMIT 10000,10; DATE INDEX: 107.577 seconds.
Using a date indexed field has a performance improvement of: 45.55%!!
So I would say if you are expecting a lot of data in your table please consider in separating the date from the time with their own index.
I tend to think there are basically no advantages to storing the date and time in separate fields. MySQL offers very convenient functions for extracting the date and time parts of a datetime value.
Okay. There can be some efficiency reasons. In MySQL, you can put separate indexes on the fields. So, if you want to search for particular times, for instance, then a query that counts by hours of the day (for instance) can use an index on the time field. An index on a datetime field would not be used in this case. A separate date field might make it easier to write a query that will use the date index, but, strictly speaking, a datetime should also work.