Как в php вносить дату в sql

Как в php вносить дату в sql

This problem describes the date format for inserting the date into MySQL database. MySQL retrieves and displays DATETIME values in ‘YYYY-MM-DD HH:MM:SS’ format. The date can be stored in this format only. However, it can be used with any time format functions to change it and display it.

When writing a query in MySQL using PHP it’s applicability will be checked on the basis of MySQL itself. So use default date and time format as provided by MySQL i.e. ‘YYYY-MM-DD’

Examples:

MySQL query to create DataBase:

Example 1: PHP program to Create database and table

Date Format in PHP for Insertion in MySQL

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.

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:

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:

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:

Here are some other PHP mktime examples:

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.

Insert current date in datetime format mySQL

I’m having problems getting the date inserted properly into my database.

I use this format, and, it echoes out correctly, however, when, I insert

it doesn’t appear to work successfully, and, the time remains 00:00:00 If you could find the solution that would be great, thanks.

15 Answers 15

If you’re looking to store the current time just use MYSQL’s functions.

If you need to use PHP to do it, the format it Y-m-d H:i:s so try

Try this instead

NOW() is used to insert the current date and time in the MySQL table. All fields with datatypes DATETIME, DATE, TIME & TIMESTAMP work good with this function.

Following code shows the usage of NOW()

you can use CURRENT_TIMESTAMP, mysql function

set the type of column named dateposted as DATETIME and run the following query:

«datetime» expects the date to be formated like this: YYYY-MM-DD HH:MM:SS

so format your date like that when you are inserting.

If you Pass date from PHP you can use any format using STR_TO_DATE() mysql function . Let conseder you are inserting date via html form

The dateposted should be mysql date type . or mysql will adds 00:00:00
in some case You better insert date and time together into DB so you can do calculation with hours and seconds . () .