I had a field set in INT, but then I realised it takes off any leading 0's. What is the best option for me to use? VARCHAR? Or is there an option to have numbers that can begin with 0?

Mathematically, there is no difference between 7 and 07 and 007.000. Whether it's in decimal or stored in a database in binary, there is no difference between the two numbers. What you are talking about is formatting of the number.

Formatting should be done with server-side code by the web server. PHP or ASP or whatever. Just write a function that treats it as a string and formats it the way you want.

All numbers start with a zero - actually they start with lots of them. Nyef already put that.

Are you wanting to do this for sorting purposes? Trying to avoid the string sort problem, where it goes 0, 1, 11, 12, 2, 25, 3, 33, etc? Obviously, storing the numbers as numbers will avoid this. If it's only for display, you can format them in the database itself (how depends on which DB system), but it's better to accept the verbatim data into your app and format them to your taste there.

The way im thinking of your problem is something like telephone numbers which are numbers but often (in UK always) start with a leading zero which will be removed if added to an INT field. ( another example might be ISBN numbers which sometimes have leading zeros. I would suggest using a varchar as they contents will never need to be manipulated mathematically and it is far simpler than formatting (and essentially guessing) if a zero should be in there or not.

Thanks, I will be using the field to store a Unix Timestamp, so I'll probably go for VARCHAR!

Thanks, I will be using the field to store a Unix Timestamp, so I'll probably go for VARCHAR!

!!??
He ?

No, use a timestampe type , and then convert that date to unix timestamp using the the unix_timestamp() function of the db.
http://dev.mysql.com/doc/refman/5.0/...unix-timestamp

Using a varchar to store a timestamp is like using smoke messages when you have a cellular phone...

Originally Posted by tripy

!!??
He ?

No, use a timestampe type , and then convert that date to unix timestamp using the the unix_timestamp() function of the db.
http://dev.mysql.com/doc/refman/5.0/...unix-timestamp

Using a varchar to store a timestamp is like using smoke messages when you have a cellular phone...

Lol, I seeee! I did see the timestamp option, but since it looked different to what Im used to seeing I simply dismissed it as being something else!

I'll set it as a timestamp then, thanks.

Tripy is correct, of course. Using the exact data type will save a lot of headache over the long run.

aghhhh Im having a lot of trouble getting my head around the timestamp format in MySQL. Whenever I try to insert a 14 digit number into it, it is still just stored in the db as '0000-00-00 00:00:00'

do I need to insert it in a way like this?

FROM_UNIXTIME ('$portDateComp')

although that does present me with an error saying that the function does not exist.

Also, Im guesing it would be a good idea to remove the 'current timestamp' option? Im using PHPMyAdmin and that was on by default.

I also set the default value containing only 0's.

Also I cant figure out how I use the UNIX_TIMESTAMP() function? Would I use that when I want to recall the record from the database and display it as a unix timestamp?

Thanks.

You can change your field type to DATETIME which also will allow you to store the date and time. The timestamp type has a limited range.

You also need to insert your datetime as YYYY-MM-DD HH:MM:SS

Query a specific datetime:

SELECT * FROM table WHERE datetime = '2008-07-06 17:50:23'

Query current datetime:

SELECT * FROM table WHERE datetime = NOW()

PHP Query specific datetime:

PHP Code:

$sql = "SELECT * FROM table WHERE datetime = '" . date('Y-m-d H:i:s') . "'" 


Return datetime:

SELECT datetime FROM table

Return datetime as unix timestamp:

SELECT UNIX_TIMESTAMP(datetime) AS unix_timestamp FROM table

Also you can use other operators with the datetime conditions.

Select all after specific datetime:

SELECT * FROM table WHERE datetime > '2008-07-01 00:00:00'

Select all between datetime range:

SELECT * FROM table WHERE datetime BETWEEN '2008-06-01 00:00:00' AND '2008-06-30 23:59:59'

Thanks for the info! Im having trouble returning it as a unix timestamp though, I currently have the following query:

$sql = mysql_query ("SELECT UNIX_TIMESTAMP(portDateComp)  unix_timestamp FROM tblPort");

echo 'RESULT: ' . mysql_result ($sql, 0); 
       
// error
if (!$sql)
    {
        $resultMessage = '<p>' . mysql_error () . '</p>';
    }

This is just part of a PHP script I have. However, the text output is just

RESULT:

With nothing after it! It doesn't seem to produce a MySQL error either which is why Im having trouble figuring out what is wrong?

Note that Ive set the field 'portDateComp' to DATETIME format, and have data in the record for that field.

You need to set the select field as an alias:
SELECT UNIX_TIMESTAMP(portDateComp) AS unix_timestamp FROM tblPort

Or no alias:
SELECT UNIX_TIMESTAMP(portDateComp) FROM tblPort

Thanks, Ive tried it both with and without an alias but the output is just '0', which is a little confusing??

Oh wait I think i was recalling the wrong record! If the record contained the default of

0000-00-00 00:00:00

Would the result output just be '0' ? Could that be what was happening?

I'd say that yes, it would output 0.

They do not represent the same date, but how to exprime a date prior to timestamp 0 ?
Surely the mysql team decided that they would leave the result to 0.

Thought it might be! Thanks everyone for the info.