Php timestamp to database

От 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.

Источник

PHP-MYSQL: Converting Unix Timestamp to DateTime and vice versa

To insert data I use php PDO and mysql NOW(). This works fine, the correct datetime is stored in the database. My plan is to work with unix timestamps on client side (php & mysql on server side). So I would like to deliver unix timestamps to my client. Therefore I use MySql UNIX_TIMESTAMP() function to convert it directly in the query. So a sample query looks like this:

SELECT created, UNIX_TIMESTAMP(created) AS TS FROM Entry 

The result: created = 2013-02-14 20:47:35 TS = 1360871255 So now I want to do the other way, I pass the a UNIX Timestamp, and want to compare it with Entries in my Database. Unfortunetly Im not able to write a PHP script that works. I don’t know why, but when I m passing the same timestamp (1360871255) to PHP I do not get 2013-02-14 20:47:35 with this method:

public static function toDateTime($unixTimestamp)

When I call toDateTime(1360871255) will return 2013-02-14 20:02:35 which is not the original DateTime. I know, I dont need to format 1360871255 to a Y-m-d H:m:s to use it in MYSQL, but 1360871255 seems not to be the time that I expected (and MYSQL UNIX_TIMESTAMP has returned). What I want to do is a simple query that shows me Entries that are older than a certain timestamp, something simple like this:

SELECT * FROM Entry WHERE created < 1360871255 

but as I mentioned before, the query result is not the expected, because 1360871255 seems not to be the correct time. I do not specify any special timezone for the mysql connection in php. Any suggestions?

Источник

Saving timestamp in mysql table using php

I have a field in a MySQL table which has a timestamp data type. I am saving data into that table. But when I pass the timestamp ( 1299762201428 ) to the record, it automatically saves the value 0000-00-00 00:00:00 into that table. How can I store the timestamp in a MySQL table? Here is my INSERT statement:

INSERT INTO table_name (id,d_id,l_id,connection,s_time,upload_items_count,download_items_count,t_time,status) VALUES (1,5,9,'2',1299762201428,5,10,20,'1'), (2,5,9,'2',1299762201428,5,10,20,'1') 

15 Answers 15

but the value you get in db shows the column type is datetime , if you want to change it to timestamp then change the column type to varchar(15)

Then put it on an INT field. Anyway, a timestamp is only a representation of a date, and vice versa. You can convert from timestamp to date with the function jimy told you, and the other way with strtotime . edit: btw, timestamp only covers a range of all possible dates (1970-01-01 to xx-xx-2032 I think)

so what is the use of timestamp datatype? s_time field has timestamp datatype. Can't I save 1299762201428 to that field?

@lakum4stackof: For timestamp datatype, please refer to dev.mysql.com/doc/refman/5.0/en/timestamp.html for detail.

@lakum4stackof - the use of timestamp is that you do save the timestamp, but it is only displayed as a date. Internally (as all data types) it's stored as signed integer. If you also want to format that timestamp as an integer, I suggest that you just use INT field then. Use of timestamp column is the date manpipulation (adding intervals and such).

Hey there, use the FROM_UNIXTIME() function for this.

INSERT INTO table_name (id,d_id,l_id,connection,s_time,upload_items_count,download_items_count,t_time,status) VALUES (1,5,9,'2',FROM_UNIXTIME(1299762201428),5,10,20,'1'), (2,5,9,'2',FROM_UNIXTIME(1299762201428),5,10,20,'1') 

It's better because web server ( PHP ) and MySQL could be in different places. So, date('Y-m-d H:i:s','1299762201428') will set the TimeZone of the web server. With different web servers placed in different zones you will have inconsistent data. Alternatively, in your code, you have to force the application timezone to the timezone of MySQL. date_timezone_set

$created_date = date("Y-m-d H:i:s"); $sql = "INSERT INTO $tbl_name(created_date)VALUES('$created_date')"; $result = mysql_query($sql); 

• MySQL timestamp field type doesn't store unix timestamps but rather a datetime-kind value.
• UNIX timestamp is a number of a regular int type.
• The timestamp you're talking about is not a regular unix timestamp but a timestamp with milliseconds.

therefore the correct answer would be

$timestamp = '1299762201428'; $date = date('Y-m-d H:i:s', substr($timestamp, 0, -3)); 

You can use now() as well in your query, i.e. :

insert into table (time) values(now()); 

It will use the current timestamp.

Datatype 'bigint unsigned' may suit this requirement.

I'm guessing that the field you are trying to save the value in is a datetime field it's not but the same seems to be true for timestamps. If so mysql expects the format to be Year-month-day Hour:minute:second. In order to save the timestamp you will have to convert the field to numeric using a query like

alter table change  bigint unsigned 

If you are using the current time you can use now() or current_timestamp.

Note: 1299762201428 looks more like a millisecond-timestamp (like Date()*1 in JavaScript), and you probably have to divide that by 1000.

Check field type in table just save time stamp value in datatype like bigint etc.

If the timestamp is the current time, you could use the mysql NOW() function

Use datetime field type. It comes with many advantages like human readability (nobody reads timestamps) and MySQL functions.

To convert from a unix timestamp, you can use MySQL function FROM_UNIXTIME(1299762201428) . To convert back you can use UNIX_TIMESTAMP : SELECT UNIX_TIMESTAMP(t_time) FROM table_name .

Of course, if you don't like MySQL function, you could always use PHP: 'INSERT INTO table_name SET t_time = ' . date('Y-m-d H:i:s', $unix_timestamp) .

The timestamp field type is human readable too (when using SELECT in the console). They are very different and both have their dis/advantages. The main difference is how time zones are being handled.

@Udo G: You are correct, but I still prefer datetime for various reasons. What is the biggest dis/advantage in your opinion?

biggest advantage for timestamp : it better matches the PHP timestamps as it is immune to time zone settings of both the server and the client, whereas datetime changes it's displayed value depending on the time zone of your MySQL client (of course it depends on your project, which one is better). biggest disadvantage for timestamp : it does not support NULL values and (I don't know what they did smoke when they programmed this) a TIMESTAMP NOT NULL field becomes a TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP . In my case I unfortunately need to use TIMESTAMP for TZ reasons.

Источник