CODEWITHSUNDEEP
phpmysqlsqldate
Simeon
Simeon

Reputation: 878

Best format for storing dates in sql

I am trying to optimise how I use dates across my SQL/PHP site. I'm familiar with date() and strtotime() functions, and have created projects which have stored data on the server side in both unix and datetime. I know that changing date doesn't use massive amounts of resource, but I'm trying to understand how to code in the most efficient way.

What I'm trying to work out now is what the quickest/most efficient practise is for storing dates on a server, i.e: What is the most effective combination of SQL FROM_UNIXTIME, CONVERT, UNIX_TIMESTAMP and PHP strtotime and date functions, for a typical table involving frequent CRUD of the date fields. Take the following example:

I have an SQL table which contains 3 date columns. All 3 of these can be updated quite regularly by multiple users of the site, and entries are also entered in to it as follows: 

mysql_query("INSERT INTO `regular` (
`regularid`,
`propertyid`,
`userid`,
`billdate`,
`billfrequency`,
`enddate`,
`amount`,
`description`,
`payee`,
`payer`,
`lastpayment` 
)
VALUES (
'', '{$_POST['propertyid']}', '$varuserid', FROM_UNIXTIME($date), '{$_POST['billfrequency']}', 'FROM_UNIXTIME($edate)', '$amount', '{$_POST['description']}', '{$_POST['payee']}', '{$_POST['payer']}', 'FROM_UNIXTIME($ldate)'
)") or die(mysql_error);

Print "A regular payment for &pound;".$_POST['amount']." has been created<br/>\n";

Entering in to the database I parse the date fields from a user entry form to a variable in mm / dd / yyyy format, validate it with the strtotime() or mktime() functions, and then submit it using from_unixtime.

However, then displaying information from the database I use: 

   date("d-m-Y", strtotime($val['billdate']))

That means I take a dd/mm/yyyy date format from the user and another one from the database. I then convert these to unixtime using strtotime, and then convert it back to British date format using date - This is surely doubling? if not tripling? the server query.

My question is in 2 parts. Firstly, is it quickest a) storing all date formats in unixtime in sql and converting to date format through the server side query or b) storing all date formats in date format and converting to unixtime only when accessing them

(I thought the latter would be best, but my example above shows that even doing that I seem to be using more server time than I ought to)

Secondly, could you point me in the right direction for how I should carry out my own time benchmarks of scripts - I'm only really beginning to understand testing as I'm still relatively new to Php, but I'm keen to learn.

Thanks so much!

Upvotes: 3

Views: 1648

Answers (1)

John Doe
John Doe

Reputation: 183

Use MySQL's "unix_timestamp()", it's short and can be converted to readable full date within a second by using php, bash etc.

Upvotes: 3

Related Questions