Php datum mysql format

Creating a PHP date in the format for a SQL Timestamp insert

PHP date/time FAQ: How do I create a date in the proper format to insert a SQL Timestamp field into a SQL database?

Note: You might not need to create a PHP date

First off, you may not need to create a date in PHP like this. If you’re using plain old PHP and a database like MySQL, you can use the SQL now() function to insert data into a SQL timestamp field like this:

INSERT INTO projects (user_id, name, last_updated, date_created) VALUES (5, 'alvin', now(), now());

I just tested this with PHP and MySQL, and it works fine. So that’s one way to populate a SQL timestamp field in a SQL INSERT query.

Creating a PHP timestamp variable

However, if you want to do this all in PHP (or need to, depending on what framework you’re working with), you can get the current date and time in the proper format using just PHP, like this:

If you print this out, your $timestamp field will now contain contents like this:

You can then use this formatted timestamp string in a PHP MySQL insert.

Note: Thanks to the commenters below who suggest using H:i:s instead of G:i:s .

A Drupal 7 SQL INSERT with Timestamp example

Although this isn’t a standard off-the-shelf PHP/MySQL INSERT statement, here’s what a SQL INSERT query looks like when I use this with Drupal 7:

$project = new stdClass(); $project->user_id = get_user_id(); $project->project_count_type = $form_state['values']['type']; $project->name = $form_state['values']['name']; $project->description = $form_state['values']['description']; # get the current time in the proper format for a sql timestamp field $timestamp = date('Y-m-d H:i:s'); # new drupal 7 style insert $id = db_insert('projects') ->fields(array( 'user_id' => $project->user_id, 'project_count_type' => $project->project_count_type, 'name' => $project->name, 'description' => $project->description, 'last_updated' => $timestamp, 'date_created' => $timestamp )) ->execute();

As you can see in the lines I’ve made bold, I’m inserting my PHP timestamp variable into two SQL fields.

Getting a timestamp for some other date and time

Note that the PHP date function defaults to the current date and time, which is exactly what I need for my purposes here. If you need to create a formatted timestamp field for some other date and time, you can do that something like this:

$timestamp = date('Y-m-d H:i:s', mktime(0, 0, 0, 7, 1, 2000));

Here are some other PHP mktime examples:

$tomorrow = mktime(0, 0, 0, date("m") , date("d")+1, date("Y")); $lastmonth = mktime(0, 0, 0, date("m")-1, date("d"), date("Y")); $nextyear = mktime(0, 0, 0, date("m"), date("d"), date("Y")+1);

I pulled those examples from the PHP date page. Please see that page for more information on creating other dates and times (I’m mostly just worried about «now» at this moment).

PHP SQL Timestamp inserts

I hope these timestamp examples have been helpful. As you’ve seen, you can generally just use the SQL ‘NOW()’ function to insert into a SQL timestamp field, but if that doesn’t work for some reason, you can also create a timestamp field in the proper format using just PHP and the date function.

Источник

Date Format in PHP for Insertion in MySQL

1. date() in PHP
2. date_format() in PHP

MySQL is an RDBMS database intended to store relational data. It supports various data types, Date being one of them. As MySQL supports only particular date formats, you need to format the dates before inserting dates into the DB; otherwise, the DB will throw an error.

This article will introduce how to format dates in PHP before inserting them into a MySQL DB.

1. DATE : YYYY-MM-DD It only stores the date without time in the range of 1000-01-01 to 9999-12-31 . For example, 2021-10-28 .
2. DATETIME : YYYY-MM-DD HH:MI:SS . It stores the date with time in the range of 1000-01-01 00:00:00 to 9999-12-31 23:59:59 . For example, 2021-10-28 10:30:24
3. TIMESTAMP : YYYY-MM-DD HH:MI:SS . It stores the date with time in the range of 1970-01-01 00:00:01 to 2038-01-09 03:14:17 . For example, 2021-10-28 10:30:24
4. TIME : HH:MI:SS . It stores the time without date in the range of -838:59:59 to 838:59:59 . For example, 10:30:24
5. YEAR : YYYY or YY . It stores the year either 4 digits or 2 digits in the range of 70(1970)-69(2069) for 2 digits and 1901-2155 | 0000 for 4 digits. For example, 2021 .

Before learning the solution, let’s understand the concept of date() .

date() in PHP

It is a built-in PHP function that returns the formatted date string.

Syntax of date()

Parameters

1. d — The day of the month in the range of 01 to 31
2. m — A numeric representation of a month in the range of 01 to 12
3. Y — A four-digit representation of a year
4. y — A two-digit representation of a year
5. H — A two-digit representation of an hour in the range of 00 to 23
6. i — A two-digit representation of a minute in the range of 00 to 59
7. s — A two-digit representation of a second in the range of 00 to 59

$timestamp : It is an optional parameter that specifies a Unix timestamp in integer format. If not provided, a default value will be taken as the current local time.

php  $formated_DATETIME = date('Y-m-d H:i:s');  echo $formated_DATETIME. "
";  // 2021-10-27 14:02:16  $formated_DATE = date('Y-m-d');  echo $formated_DATE. "
";  // 2021-10-27   $formated_TIME = date('H:i:s');  echo $formated_TIME. "
";  //14:03:57   $formated_YEAR = date('Y');  echo $formated_YEAR. "
";  // 2021  ?> 

2021-10-27 14:02:16 2021-10-27 14:03:57 2021 

date_format() in PHP

It is a built-in PHP function that takes the DateTime object as input and returns the formatted date string.

Syntax of date_format()

date_format($dateObject, $format); 

Parameters

$dateObject : It is a mandatory parameter that specifies a DateTime object.

1. d — The day of the month in the range of 01 to 31
2. m — A numeric representation of a month in the range of 01 to 12
3. Y — A four-digit representation of a year
4. y — A two-digit representation of a year
5. H — A two-digit representation of an hour in the range of 00 to 23
6. i — A two-digit representation of a minute in the range of 00 to 59
7. s — A two-digit representation of a second in the range of 00 to 59

php  $date = date_create("2021/10/27");   $formated_DATETIME = date_format($date, 'Y-m-d H:i:s');  echo $formated_DATETIME. "
";   $formated_DATE = date_format($date, 'Y-m-d');  echo $formated_DATE. "
";   $formated_TIME = date_format($date, 'H:i:s');  echo $formated_TIME. "
";   $formated_YEAR = date_format($date, 'Y');  echo $formated_YEAR. "
"; ?> 

2021-10-27 00:00:00 2021-10-27 00:00:00 2021 

Shraddha is a JavaScript nerd that utilises it for everything from experimenting to assisting individuals and businesses with day-to-day operations and business growth. She is a writer, chef, and computer programmer. As a senior MEAN/MERN stack developer and project manager with more than 4 years of experience in this sector, she now handles multiple projects. She has been producing technical writing for at least a year and a half. She enjoys coming up with fresh, innovative ideas.

Related Article — PHP MySQL

Источник

Use PHP MySQL date format correctly

Learn how to handle PHP and MySQL date formatting correctly.

If you ever try to format date in PHP and MySQL, using date (datetime or timestamp) value directly from MySQL to PHP date() function, then you’ll get a Notice level error, something like this:

Notice: A non well formed numeric value encountered in . .. .

Additionally, PHP date() will always return January, 01 1970 if MySQL date is directly used. It means, there is a difference between date in PHP and date in MySQL (i.e. they are not compatible). PHP date function accepts Unix Timestamp, which is different from MySQL date types (DATE, TIME, DATETIME, TIMESTAMP etc.)

So if you want to change your default MySQL date format (the way it looks) to a more suitable formatting using PHP date() function, then you’ll have to convert MySQL date to Unix Timestamp.

In this PHP MySQL tutorial, I’ll show you how to use MySQL DATETIME or TIMESTAMP data in php date() function correctly to achieve different types of formatting.

The first task is to choose MySQL date type correctly, so that it can be used in PHP date() function without error.

As I already mentioned, MySQL DATETIME or TIMESTAMP data is not saved as UNIX Timestamp, which is required for PHP date function. So, first of all, in your SQL query, you’ll have to select MySQL date properly. There is a MySQL function called UNIX_TIMESTAMP(), use that function in you MySQL query to make PHP-MySQL date compatible. For example:

SELECT UNIX_TIMESTAMP(`Your_Date_Column_Name`) AS date FROM `Your_Table_Name` WHERE 1 // replace Your_Date_Column_Name and Your_Table_Name with appropriate table name and column name.

Your MySQL date is now in correct shape to be used in PHP date() function (in the second parameter).

The second task is to use the formatting string correctly. PHP date() function takes a formatting string as the first parameter, which is a combination of several predefined key characters and other redundant characters (not predefined) of our need. For example, to achieve a specific date format, say, Month, date year Hours:min:sec, use the following PHP code:

// the variable $date_from_mysql should have the date value from MySQL which is shown above $date = date('F, d Y H:i:s', $date_from_mysql); // this returns something like: January, 03 2011 03:45:17

In the above CODE, the following characters are predefined key characters: F|d|Y|H|i|s, on the other hand space, comma and colon characters are for our desired formatting only. In the above example, F returns the full month name (January), d returns two digit date (03), Y returns four digit year (2011) and so on. Combining with comma, colon and space the complete date becomes January, 03 2011 03:45:17. Meaning of all the key characters are listed in PHP date() function manual.

Let’s use another example. Say, we want to achieve the following date formatting: Hours:mins AM/PM, Day (Month Date, Year)

$date = date('h:i A, l (F d, Y)', $date_from_mysql);

This will return a date string like this: 11:53 AM, Wednesday (January 12, 2011)

Or, say, we want to represent a date like this, 12th January, 2011

$date = date('jS F, Y', $date_from_mysql);

Off course, if you don’t provide the second parameter in PHP date() function, then it will show the current date-time 🙂

$date = date('M jS, Y g:i A [e]');// e stands for timezone. // this will return something like: Jan 18th, 2011 2:32 PM [UTC]

Addition Resources for further reading:

Источник