converting time to timestamp

vince8

Hello!

I want to take some redundancy out of my database. This is because I have a field that saves the time by 'now()' (MySQL) in a datetime-field and another field, that saves a unix-timestamp as integer.

well, for my script I need both, but I want the datetime-value to stay in the database because it is much more readable whereas a timestamp is nothing more than a number to read.
so I want to delete the field that saves the timestamp, but how can I get the timestamp from the date which is formatted like "0000-00-00 00:00:00"??!

has anyone an Idea?

yours,
vince

hand

SELECT date_format(timestampdatefieldintable, '%Y-%m-%d %H:%i:%s') FROM table;

...

Update table set newdatetimefield = date_format(timestampdatefieldintable, '%Y-%m-%d %H:%i:%s');

vince8

Originally posted by hand
SELECT date_format(timestampdatefieldintable, '%Y-%m-%d %H:%i:%s') FROM table;
...
Update table set newdatetimefield = date_format(timestampdatefieldintable, '%Y-%m-%d %H:%i:%s');

hi! thanks for your answer.
your idea is to alter the query? but what is the update-comand for? another thing that confuses me is if there is a way to combine the query I use to get the hole table (select * from table where...) with the one you mentioned? or do I have to write two query-statements?

Is there a way to get the timestamp using PHP in my script with the datetime-information? wouldn't that be a smarter way to go?

please write some lines to make your pelp a bit more unnderstandable.
thanks
vince

hand

The update statement - which I didn't test - is to convert your old db timestamp entries to your new datetime database field if you want to convert them.

If you want to eliminate the part which generates your unix-timestamp then you can use date() function of php http://www.php3.de/manual/en/function.date.php to convert the timestamp integer to the mySQL needed format "0000-00-00 00:00:00".

I don't know if the script generates the actual life timestamp, but if so, you can use Mysql now() command instead

insert into table (dateintable,b,c,) values (now(),'content ofb', '...');
http://www.mysql.com/doc/en/Date_and_time_functions.html

You can also alter your query:
SELECT *,date_format(timestampdatefieldintable, '%Y-%m-%d %H:%i:%s') AS hand FROM table;

So you get an additional field named "hand".

vince8

hello again!

thank you for the explanation.

The update statement - which I didn't test - is to convert your old db timestamp entries to your new datetime database field if you want to convert them.

well, fortunately I don't have to convert anything into timestamp, because they are already stored in the database.

If you want to eliminate the part which generates your unix-timestamp then you can use date() function of php http://www.php3.de/manual/en/function.date.php to convert the timestamp integer to the mySQL needed format "0000-00-00 00:00:00".

What I'm looking for is quite the opposite; I need the timestamp from the mySQL format, not the other way round.

I found the function strtotime()
-> int strtotime ( string time [, int now])
which is expected to convert datetime into unix-timestamp. But there the manual says that id does it (quote) "relative to the timestamp given in now"... but what does that mean?

if my date is "2002-09-05 00:00:01" how would the funktion look like to get the appropriate timestamp?

like this?: strtotime("2002-09-05 00:00:01", 0);?
if not, could you tell me the right syntax?

thank you a lot,
vince

PS:

You can also alter your query:
SELECT *,date_format(timestampdatefieldintable, '%Y-%m-%d %H:%i:%s') AS hand FROM table;

This is great ;-)

hand

SELECT *,UNIX_TIMESTAMP('2002-09-05 00:00:01') AS hand FROM table;

http://www.mysql.com/doc/en/Date_and_time_functions.html