[cnapsys]

Hi all,
I have a few questions about storing php timestamps in mysql tables.
What is the best mysql type field for storing a unix timestamp?
What are the advantages of using varchar type or int type? Any other type i should consider?
My problem is that i will be having a lot of scripts for comparing the date fields and I just wanna make sure I do my best from the beginning.
There will be a lot of entries in the db and i'm looking for the most efficient memory and processing usage as well as fastest time for queries.

Bottom line:
What is the best type? Why?

[DAL]

Best type:I would imagine the date field would be the best for dates.
Why:Because it was made for dates.

VarChar is limited to 255 characters which as you know is better for access as the memory allocation for this field is low.

Int is a general value integer. consider tinyint where you can get away with it.

I use
varchar for titles and names,
int for index record numbers and large stock qantities,
float for currency and numbers with sigfig
tinyint for small quantities and low count processing,
text for text which needs no formatting,
longtext for my unlimited entry fields (usually HTML Scripts and such)
Date of YYYY-MM-DD HH:MM:SS:MS for this. I think you can preformat this field but Ive never needed to, I just switch it all around using a PHP function I wrote years ago.

Thats pretty much me. Im not too knowledgable on DB's and PHPMyAdmin has buttons I may someday dream of pressing and understanding.


Hope this helps
Dal.

[Jay6390]

For a date, use type DATE,
for a time, use type TIME,
for a date and time, use type DATETIME

Really is that simple lol

[UnrealEd]

Originally Posted by DAL

VarChar is limited to 255 characters which as you know is better for access as the memory allocation for this field is low.

But it is much slower to access as your mysql server will have to determine the length of the field (of each record in your table) everytime it wants to read in the value of it. A CHAR might use a little more memory (it all depends on what you're going to store in it), but is a lot faster as it has a fixed with.
This means that the filepointer can simply jump to the next field, whereas, with a VARCHAR, it first needs to read the first byte of the field to determine the length of the total field, and then jump to the next field

Originally Posted by DAL

Int is a general value integer. consider tinyint where you can get away with it.

I doubt the fact that cnapsys would get away with a TINYINT (1 byte) as the values only range from -127 to 127 if it's a signed INT, and 0 to 255 if it's not. If I recall correctly, a unixtime has a value with at least 9 digits.
He should at least use an INT (4 bytes) or even a BIGINT (8 bytes)


As for the DATE, TIMESTAMP and TIME functions, have a look at the DATETIME, DATE and TIMESTAMP Types in the MySQL manual



Oh yes, I'm moving this to the Database forum, as this is more of database related problem

[cnapsys]

I really appreciate your help guys.
However, my question was on storing a unix timestamp which is the amount of seconds from January 1, 1970.
It will not be in the format of YYYY-MM-DD HH:MM:SS:MS... that is a DATETIME type or TIMESTAMP, but I will basically be working with integers.
E.g. the following date 2008-03-27 21:08:00 has the following Unix timestamp: 1206670080
The reason for storing it as a unix timestamp is for easier comparison within the scripts.
I'm just trying to wheigh my options and see why some people choose to store the value in a varchar field while others prefer int.

Please shed some light.
Thank you

[UnrealEd]

It's very easy to convert a DATE, TIME or DATETIME field to a unix timestamp, using the UNIX_TIMESTAMP function. It still is best to save it as either a DATE, TIME or DATETIME field. simply convert the unixtime to a DATETIME object using a very simple php workaround:

PHP Code:

mysql_query (sprintf ("INSERT INTO table (`date`) VALUES ('%s')", date ("YmdHis", time ()))); 

If you still want to store the unixtime itself, you'll have to use an INT field or a CHAR/VARCHAR field

[cnapsys]

Hey Ed, and thx again for your fast reply...

here's my situation:
Lets take the following 3 dates

PHP Code:

$a=date("2008-03-28 18:00:00"); //today's date... random time

$b=date("2008-03-31 18:00:00"); //random date and time within the week's interval

$c=date("2008-04-04 18:00:00"); //end date... a week from today 

the following:

PHP Code:

$atime=strtotime($a);

$btime=strtotime($b);

$ctime=strtotime($c); 

returns:
1206745200
1207004400
1207350000

You can clearly see why I wanna choose storing these values into MySQL

Lets say we have a quick small search engine that it is supposed to display events between the following dates: 2008-03-28 and 2008-04-04.
By storing the unix timestamp into mysql its a lot easier to build the queries by conditioning the time interval the results should fall within.

If I chose to store the values in the YYYY-MM-DD HH:MM:SS:MS format I'd have to go thru a lot of explosions and arrays to compare each segment of the date to each other.

[Jay6390]

Originally Posted by cnapsys

If I chose to store the values in the YYYY-MM-DD HH:MM:SS:MS format I'd have to go thru a lot of explosions and arrays to compare each segment of the date to each other

Not true. MySQL has a number of functions built in to prevent these kind of problems. See here for a list of them

[cnapsys]

wow... thanks for the resource Jay... never checked that before...
I'll give it a quick look and see if I can implement that into my system...

Gotta love the open source communities... you learn something new every day!

[Jay6390]

Yup indeedy