Working with date/time

[eluser]garymardell[/eluser]
Hey,
I need some help or more some guidance.
I often have to work with dates/time for things such as date registered or date/time of creation. Now these dates usually have to be stored in mysql and then interpreted by php.

Yet the problem remains i never know:
What datatype to store them as in mysql.
What functions to use to get the correct format for saving.
How to go about making text such as x minutes ago.
How to perform time/date comparison such as comparing a time to now.

And just general working with dates and times.
If someone could enlighten me on the best practices i would appreciate it greatly.

[eluser]TheFuzzy0ne[/eluser]
I use [url="http://www.tizag.com/mysqlTutorial/mysql-date.php"]this[/url] as my guide when it comes to figuring out what format a date must be in to insert it into MySQL.

As for your other questions, they are actually quite complex, as it depends on how what you are doing. If you wouldn't mind posting some code with your attempts to implement your solution, we may be able to give you some pointers. I say that because I am not export on things like this myself, but I usually manage to get things working the way I want eventually.

Another good source is:
http://uk2.php.net/manual/en/ref.datetime.php

I know it seems confusing, but I promise you, if you study that initial page for about an hour, it soon starts sticking in your head. Although I can't remember what half of the functions do off the top of my head, it's relatively easy to find what I am after by looking at that page.

Hope this helps.

[eluser]helmutbjorg[/eluser]
Just an idea for you that I have been using for a while to simplify what you are talking about. Use a php timestamp and store it in the database instead. That way your code can be used across any type of database. You can then still sort it and get ranges etc using native php functions instead of relying on the database tools.

For example i have a column called 'date' and i can insert the current date and time using php's date('U') function which returns the current unix timestamp.

[eluser]Developer13[/eluser]
I agree with helmut - use a unix timestamp. Not only can you access it across various databases, but you can also format it to your exact liking in the output with many different functions.

[eluser]Jonas G[/eluser]
I prefer saving date and time in database format (field type DATETIME) just in case you want some of the mysql date functions. You can save in this format by using inserting:

Code:

date('Y-m-d H:i:s', time())

And to back to UNIX timestamp by

Code:

$time = strtotime($my_previously_inserted_date)

I find that this way of doing things might be a little more lines of code but it doesn't limit your options. And you can actually save some lines of code by working with mysql attributes like ON UPDATE CURRENT_TIMESTAMP and the default value of CURRENT_TIMESTAMP

[eluser]internut[/eluser]
I like the unix timestamp way of things... Anyone got some info on purging records from a table if so old? hmm Imagine I could get a unix time stamp of a specific date and remove anything less?

Any guidence welcome.

[eluser]TheFuzzy0ne[/eluser]
That's exactly what you'd do.