getting time from timestamp

[xianaier]

Hey
I was just wondering if there is a way to get a time from a timestamp. I have a database of songs and all the lengths are timestamps. I want to change those to to times.

Thanks for any help!

[Stanley Fisher]

Too often I see scripts query to insert a timestamp obtained with PHP time () in a field INT, if not in a varchar field, even worse, a float!!A picture of the structure a table taken from a tutorial I found on the web. To be honest it is the perfect example:

Scary when you think! And then the float to an integer, it's crazy!
usually found it easier to INT (15) or BIGINT (20) pretty wacky stuff, and it must be admitted.

And for the code, there are things like this: <? php / / query and timestamp php $ query = mysql_query ("INSERT INTO table VALUES ('', '". $ val1. "'". $ val2. "'". time ( ). "')"); / / and for display, it is weighted: echo '', date ('d / m / Y H \ hi', $ data ['champ_time']),'; ?>

I am telling you right now, I hate it! It is totally stupid to do so and it proves that those who recommend it do not know about them. The time () function should only used to display the current date and nothing else. Mysql field has typically reserved dates and times. In addition to having this type of field to take advantage of all options and functions authorized calculates Mysql.

[dWight]

I think first of all you need to understand what is the difference between two TIMESTAMP?

Unix timestamp (PHP) is the elapsed time in seconds since the 01/01/1970. The notation is read as a number: 123456789. The timestamp Mysql is a format for writing against a maximum of 14 digits representing a date or the moment with the command mysql NOW () The rating is Mysql timestamp as YYYY-MM-DD HH: MM: SS or YYYYMMDDHHMMSS. For example 2011-10-15 20:20:15 20111015202015 or So in summary:

UNIX timestamp (php) = number of seconds since 1/1/1970
type = mysql timestamp a date, time, reference time


So, with that you are ready. I show you now the right way to do this: First, we replace the timestamp field by giving it another name as a reserved word in Mysql. Although some words are perfectly acceptable, as action, date, time, timestamp, etc ... we need to know that their job requires the use of improper exhaust characters: `date` My advice to avoid any risk: do not use them!

Aa reminder, you can see the full list here: Cases Reserved Words Let's go and replace the smoky timestamp type FLOAT with the right kind of field: - we'll just call it time and given the type DATETIME more simple you can not and your request to save the time of your entry becomes:

<? php $ query = mysql_query ("INSERT INTO table (id, title, content, time) VALUES ('', '". $ title."' ". $ content." 'NOW ())"); ?> And now, the time of your online news by example is registered with NOW () DATETIME format is a string formatted as follows: 'YYYY-MM-DD HH: MM: SS'

You can now make a request of this type for example: <? php $ req = " SELECT title, DATE_FORMAT (time, '% d-% m-% Y') as time FROM table WHERE id = 1 "; $ res = mysql_query ($ req); $ data = mysql_fetch_assoc ($ res) echo 'news ', $ data [' title '],' was posted on ', $ data [' time '] ?>

And now, all the ease of a choice! With this type DATETIME (or TIMESTAMP or DATE) and this chain, you can now enjoy all the features and time calculations provided by MySQL. The three types of fields mentioned above are available here: Cf Doc Mysql -> The DATETIME, DATE, and TIMESTAMP. The list of features, quite impressive, is there: Cf Doc Mysql -> Functions Date and Time Your turn, I leave you the pleasure of discovering BETWEEN, YEAR, MONTH, DAY, and other functions just as practical. Ya ka could, to quote another!

I give you a few examples: <? php / / Query with selection of one month $ req = "SELECT title FROM thetable WHERE MONTH (time) = 12", / / a query with an interval of two years $ req = "SELECT title FROM thetable WHERE YEAR ( time) BETWEEN 2010 AND 2011 ", / / another query with an interval $ req = "SELECT title FROM thetable WHERE NOW ()-INTERVAL DAY 3 = time"; ?> A small final for the road. Here's one that will return all dates between 10 and 20 days prior to today. "php $ req = "SELECT title FROM thetable WHERE time BETWEEN DATE_SUB (NOW (), INTERVAL 10 DAY) AND DATE_SUB (NOW (), INTERVAL 20 DAY) "; ?>

[deepak11627]

hi,
PHP has a strong support in date, time functions. You can use strtotime() to convert a string into time. then just use date function to get the time in required format.
See the following eg.

echo date('D-m-y;, strtotime($timestam));

This will display the time from the passed string.
Let me know if you still find it difficult. <img src='http://imagicon.info/cat/6-24/icon_smile.gif'>