Sql timestamp to date php

От PHP к MySQL сквозь время

Порой нам необходимо переводить время из одного формата в другой. Например, нужно время из PHP-скрипта сохранить в базе данных MySQL. Всё бы ничего… но они по-разному работают со временем. И новички порой строят огромные костыли, чтобы, как им кажется, упростить свою жизнь.

Для этой статьи я возьму в пример метки времени PHP и формат времени MySQL.

PHP работает с метками времени UNIX. То есть время представляется в виде количества секунд, прошедших с начала дня 1 января 1970 года. И на момент написания статьи — 23:25 17 июня 2015 года — PHP показал вот такое время — 1434573224. А вот в MySQL время хранится в другом формате (типе данных). Я предпочитаю тип DATETIME. Упомянутая выше дата выглядела бы в таблице MySQL в поле с типом DATETIME так: 2015-06-17 23:25:00.

Перевод времени из PHP в MySQL

Ну так как же нам перевести время из PHP-скрипта в MySQL? Очень просто. Для начала, давайте решим, откуда в вашем скрипте берется какая-либо метка времени? Допустим, время регистрации пользователя является временем работы скрипта. В этом случае нам достаточно использовать встроенную в PHP функцию time(). Она и вернет текущую метку времени UNIX. А если вам нужно сохранить в базе данных дату, которую пользователь ввел в HTML-форму? Тут задача немного сложнее. Но она сама по себе несложная.

)\.(\d)\.(\d)$/'; $result = preg_match( $pattern, $sUserDate, $matches ); if ( $result && !empty( $matches ) ) < $day = $matches[1]; $month = $matches[2]; $year = $matches[3]; >$time = mktime( 0, 0, 0, $month, $day, $year ); // Получим метку времени 1435006800 ?>

И как теперь сохранить это в базу? Да еще и в формате типа данных MySQL DATETIME… Легко!

Нувотивсёбырь Задача решена! Проверьте 🙂

Перевести время из MySQL в PHP…

…могло бы оказаться сложнее, но нет. Это так же просто. Нет, пожалуй, это еще проще.

Скажу вам больше. Если функции UNIX_TIMESTAMP() не передавать вообще никаких аргументов, результат будет тем же. Вам вернется текущая метка времени UNIX. И делайте с ним в своем скрипте теперь то, что хотите.

Перевод времени из PHP в MySQL и из MySQL в PHP оказался делом плёвым. Вообще ерундовым. Я надеюсь, подключаться к своему СУБД вы уже умеете?

Подобные приемы очень часто нужны при написании алгоритмов регистрации и авторизации пользователей, а также просто при работе пользователя с HTML-формами, в которых он вводит дату и еще работает с динамическими списками select.

Источник

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.

Источник

Converting a PHP timestamp to a date(time)

In this PHP tutorial, we’re going to convert a timestamp in PHP to a date (optionally with time) using custom formatting. We’ll start by getting the current time stamp and display it using the simple date function. Next, we’ll use the current timestamp to create a Date Time object, format the date and display it. We’ll then view some of the date and time formatting options, followed by the Date Time Zone class. And finally, we’ll create a function to convert a timestamp to DateTime with time zone and date format parameters.

In this article

Getting a Timestamp

Let’s start with a basic PHP function to get the timestamp, we’ll use time() to get the current timestamp and date to format the output.

The time stamp saved in $now is similar to 1610246191, so we format it using date(‘Y-m-d’, $now) for a user friendly output. The timestamp returned is in seconds, see the microtime function to get a timestamp including micro seconds.

Timestamp To Date Time Object

While we can use the date function to format our date or timestamp, it isn’t the object oriented way of doing it. Let’s create a DateTime object and set the timestamp.

We can then output the date and time using the format method with the ‘c’ datetime parameter.

Formatting a Date

The format method can format a date in many different ways, common formats are: Y-m-d and d/m/Y .

The PHP manual has many more date formats available.

Formatting a Time

The DateTime class can also format the same timestamp in time only format.

We use the H:i:s(Hour, Minute, Second) format in the code above.

Date with TimeZone

The DateTime object can be set to use a specific time zone. We create a new DateTimeZone with a particular time zone and call the setTimeZone method on our DateTime object.

The DateTimeZone class has many timezone options. The PHP code example above displays the same timestamp using different time zones.

Converting a Timestamp to Date Time

Our final function is a combination of DateTime and DateTimeZone with 3 parameters: a timestamp, time zone and date/time format with default formatting.

Our custom PHP function timeStampToDateTime takes a timestamp as first parameter, which could be the current timestamp or any other time stamp (maybe a timestamp from a db). It uses the timezone and format to return a human readable date time string.

Key Takeaways

• We can use the time() function to get the current timestamp and the date function to format a timestamp.
• The DateTime class is the object oriented way of working with dates and times.
• The DateTimeZone class provides time zone functionality which can be used with the DateTime class to work with dates and times.
• The format , setTimeZone and setTimeStamp methods are the primary methods we can use to convert a timestamp to date/time.
• Related PHP functions: microtime

This is the footer. If you’re reading this, it means you’ve reached the bottom of the page.
It would also imply that you’re interested in PHP, in which case, you’re in the right place.
We’d love to know what you think, so please do check back in a few days and hopefully the feedback form will be ready.

Источник

Sql timestamp to date php

• How to get the current Date and Time in PHP ?
• PHP program to change date format
• How to convert DateTime to String using PHP ?
• How to get Time Difference in Minutes in PHP ?
• Return all dates between two dates in an array in PHP
• Sort an array of dates in PHP
• How to get the time of the last modification of the current page in PHP?
• How to convert a Date into Timestamp using PHP ?
• How to add 24 hours to a unix timestamp in php?
• Sort a multidimensional array by date element in PHP
• Convert timestamp to readable date/time in PHP
• PHP | Number of week days between two dates
• PHP | Converting string to Date and DateTime
• How to get last day of a month from date in PHP ?

PHP String Based

• PHP | Change strings in an array to uppercase
• How to convert first character of all the words uppercase using PHP ?
• How to get the last character of a string in PHP ?
• How to convert uppercase string to lowercase using PHP ?
• How to extract Numbers From a String in PHP ?
• How to replace String in PHP ?
• How to Encrypt and Decrypt a PHP String ?
• How to display string values within a table using PHP ?
• How to write Multi-Line Strings in PHP ?
• How to check if a String Contains a Substring in PHP ?
• How to append a string in PHP ?
• How to remove white spaces only beginning/end of a string using PHP ?
• How to Remove Special Character from String in PHP ?
• How to create a string by joining the array elements using PHP ?
• How to prepend a string in PHP ?

PHP Class Based

PHP JSON Based

PHP File Systems Based

Источник

Convert a Timestamp to a Readable Date or Time in PHP

1. Use date() Function to Convert a Timestamp to a Date/Time in PHP
2. Use setTimestamp() Function to Convert a Timestamp to a Date in PHP
3. Use createFromFormat() Function to Convert a Timestamp to a Date in PHP

In this article, we will introduce methods to convert a timestamp to date in PHP.

• Using date() function
• Using setTimestamp() function
• Using createFromFormat() function

Use date() Function to Convert a Timestamp to a Date/Time in PHP

The date() function converts a timestamp to a human readable date or time . The correct syntax to use this function is as follows

It has two parameters. The parameter $format is the date-time format that the timestamp is converted to. The other parameter $timestamp is an optional parameter. It gives the date according to the timestamp passed. If it is omitted, it uses the current date by default.

php $date = date('d-m-Y H:i:s', 1565600000); echo "The date is $date."; ?> 

The date format here is day-month-year , and the time format is hour:minute:second .

The date and time are 12-08-2019 08:53:20. 

Use setTimestamp() Function to Convert a Timestamp to a Date in PHP

The built-in setTimestamp() converts the given timestamp to date or time . To set the format of the date we will use format() function.

$datetimeObject->setTimestamp($timestamp); 

php $date = new DateTime(); $date->setTimestamp(1565600000); $variable = $date->format('U = d-m-Y H:i:s'); echo "The date and time is $variable."; ?> 

The date and time are 1565600000 = 12-08-2019 08:53:20. 

Use createFromFormat() Function to Convert a Timestamp to a Date in PHP

The built-in function createFromFormat() gets the date by passing the timestamp as a parameter to this function.

DateTime::createFromFormat($format, $time, $timezone); 

The variable $format is the format of the date, $time is the time given in string and $timezone tells about the time zone. The first two parameters are the mandatory parameters.

php // Calling the createFromFormat() function  $datetime = DateTime::createFromFormat('U', '1565600000'); // Getting the new formatted datetime  $date= $datetime->format('d-m-Y H:i:s'); echo "The date and time is $date."; ?> 

The format «d-m-Y H:i:s» displays both date and time .

The date and time are 12-08-2019 08:53:20. 

Related Article — PHP Timestamp

Related Article — PHP DateTime

Источник