Data format in application or database?

[turnitup]

I have found that by far the easiest way to store dates in my LAMP
application is in a text string in the database in Unixtime format.

First of all, what would be the most efficient data type to store this as?

Secondly, in terms of translating into a human readable format, is it
quicker to format the date in the application using date() or in the
database in the select query?

Any thoughts?

[Jeff North]

Re: Data format in application or database?

On Sat, 09 Sep 2006 09:40:03 +0100, in comp.lang.php turnitup
<same@same>
<45027de4$0$137 7$da0feed9@news .zen.co.ukwrote :

>| I have found that by far the easiest way to store dates in my LAMP
>| application is in a text string in the database in Unixtime format.
>|
>| First of all, what would be the most efficient data type to store this as?

Numeric format.

>| Secondly, in terms of translating into a human readable format, is it
>| quicker to format the date in the application using date() or in the
>| database in the select query?
>|
>| Any thoughts?

Its much the much either the database server or the web server needs
to do the processing. You'll need to decide which one can carry the
load.

I prefer the database to handle such things as dates and times. They
have their own native (interal) formats for storing the data and thus
more efficient at retrieval and processing.

By storing the time as text you loose the ability to:
1. do any date/time calculations
2. restrict or delete records between date/time ranges
3. do date/time comparisons.
---------------------------------------------------------------
jnorthau@yourpa ntsyahoo.com.au : Remove your pants to reply
---------------------------------------------------------------

[larry@portcommodore.com]

Re: Data format in application or database?


turnitup wrote:

I have found that by far the easiest way to store dates in my LAMP
application is in a text string in the database in Unixtime format.
>
First of all, what would be the most efficient data type to store this as?
>
Secondly, in terms of translating into a human readable format, is it
quicker to format the date in the application using date() or in the
database in the select query?
>
Any thoughts?

Unless you are doing something that involves 'real-time' (as in in
relatrion to events like logging data) the Unix time stamp to me seems
to be a pain, as most of my work resolves down to the day, or in
general time of day (down to the minute). Also because of daylight
savings is factored in you may run into unexpected problems in date
calculations.

I have written up functions to convert to/from an 8 digit interger for
date recording: YYYYMMDD which as been used on earlier development
environments (like FoxBase), It does not suffer from 'when's the epoch'
or running out of seconds in 2038. That format is easy to sort by and
you can use PHP to convert it into a timestamp value if you want to do
math on it.

For time I also use an interger representing number of minutes since
midnight (1440 in a 24hour period), though not human readable it is
math friendly.

This is my preference, my reasonong is to make it generic so data can
cross between systems, but human readable enough if there is no machine
involved. Your mileage will vary on who you ask. Stick with what
works for you,

Larry

[Alvaro G. Vicario]

Re: Data format in application or database?

*** turnitup escribió/wrote (Sat, 09 Sep 2006 09:40:03 +0100):

I have found that by far the easiest way to store dates in my LAMP
application is in a text string in the database in Unixtime format.
>
First of all, what would be the most efficient data type to store this as?

What's wrong with DATE and DATETIME?


--
-+ http://alvaro.es - Álvaro G. Vicario - Burgos, Spain
++ Mi sitio sobre programación web: http://bits.demogracia.com
+- Mi web de humor con rayos UVA: http://www.demogracia.com
--

[Tony Marston]

Re: Data format in application or database?

The best way to store a date is as a DATE datatype in YYYY-MM-DD format so
you can do comparisons between different dates. How you display them to the
user is another problem. I prefer not to format any dates until as late as
possible, just before I present them to the user.

--
Tony Marston

http://www.tonymarston.net

http://www.radicore.org

"turnitup" <same@samewro te in message
news:45027de4$0 $1377$da0feed9@ news.zen.co.uk. ..

>I have found that by far the easiest way to store dates in my LAMP
>application is in a text string in the database in Unixtime format.
>
First of all, what would be the most efficient data type to store this as?
>
Secondly, in terms of translating into a human readable format, is it
quicker to format the date in the application using date() or in the
database in the select query?
>
Any thoughts?

[Jerry Stuckle]

Re: Data format in application or database?

turnitup wrote:

I have found that by far the easiest way to store dates in my LAMP
application is in a text string in the database in Unixtime format.
>
First of all, what would be the most efficient data type to store this as?
>
Secondly, in terms of translating into a human readable format, is it
quicker to format the date in the application using date() or in the
database in the select query?
>
Any thoughts?

I use the DATE type that's defined in the database. That's what it's
there for.

You can format the data in almost any way you want using database
functions when retrieving the data. Additionally, you can easily
compare dates in the database.

--
=============== ===
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attgl obal.net
=============== ===