int(11) and unix timestamp

[Jakeman]

What is the biggest number / date that can be stored in a field of type int(11)? Just curious.

:cheeky:

[Dark_Wizard]

Theoretically the largest would be 99999999999 which would produce the date of: Wed, 16 Nov 5138 09:46:39 UTC

[Andreas]

I somewhat doubt we'll use mySQL in year 5138 ^.^

[Dark_Wizard]

I somewhat doubt we'll use mySQL in year 5138 ^.^

LOL...agreed. I wonder what it would be like in 5138?

[Xenon]

actually dark wizard is not correct here.

INT datatype is a 32 Bit integer, so if signed, then the biggest value is 2^31 - 1 = 2.147.483.647

the (11) is useless as no int 32 can ahve more than 10 chars.

so to using the calculator gives you '19 Jan 2038' as biggest timestamp.

[Modin]

Yep, that's the "real" y2k scare Xenon :D

though technically a timestamp is of type LONG not INT, INT's are only 16bits while LONG's are 32bits (on most architectures) ;)

[Jakeman]

So I've got some time before I have to worry. :eek:

[Xenon]

Ahh, time flies Jake ;)

@Modin: thechnically LONG is just the short form of LONG INTEGER, so INT is the correct thing here ;)

[Dark_Wizard]

actually dark wizard is not correct here.

INT datatype is a 32 Bit integer, so if signed, then the biggest value is 2^31 - 1 = 2.147.483.647

the (11) is useless as no int 32 can ahve more than 10 chars.

so to using the calculator gives you '19 Jan 2038' as biggest timestamp.

Interesting...hey at least I gave it a shot. ;)

[Xenon]

yay ;)

you cannot be correct everytime ;)

even i make mistakes (at least the others say so ^^)

[Dark_Wizard]

yay ;)

you cannot be correct everytime ;)

even i make mistakes (at least the others say so ^^)

LOL...you make mistakes? Hmmm...now I'm upset. ;)

[Modin]

@Modin: thechnically LONG is just the short form of LONG INTEGER, so INT is the correct thing here ;)

touche, depends if you're referring to the math representation of an int, or the computer representation ;)

Never thought I'd be debating number precision :p

One thing that puzzles me though, does the timestamp really have to be signed? How often do we need to store dates from Fri Dec 13, 1901 ;)

[Xenon]

depends if you're referring to the math representation of an int, or the computer representation

well, actually INT is just the term for numbers, so no rational or others.

if you go to the computer representation, you can see each language uses it's own values and restrictions for the datatypes.
in SQL INT is th 32 Bit integer, as it is in php as well.
in C if you use INT it means 16 Bit, but actually you can use long int to get 32 Bit (in the old C compileres the int was needed after the long, just newer ones accept long alone as the shortform of long int :))

but, right, i never thought i would discuss about such things ^^

as for the signed question. MySQL allows unsigned int as well, but in php's date function you'll get an error if you try to translate an unsigned int into a timestamp (just tested ;))

@Dark: *gg*

[Modin]

well, actually INT is just the term for numbers, so no rational or others.

if you go to the computer representation, you can see each language uses it's own values and restrictions for the datatypes.
in SQL INT is th 32 Bit integer, as it is in php as well.
in C if you use INT it means 16 Bit, but actually you can use long int to get 32 Bit (in the old C compileres the int was needed after the long, just newer ones accept long alone as the shortform of long int :))

but, right, i never thought i would discuss about such things ^^

as for the signed question. MySQL allows unsigned int as well, but in php's date function you'll get an error if you try to translate an unsigned int into a timestamp (just tested ;))

Any language like SQL or PHP can fake a 32bit int(store it as an array of 4 bytes, or, if supported use the hardware LONG), though from a hardware standpoint, INTs are only 16bit. :)

as for the signed question, I should have been a little more clear...it was why is the unix timestamp signed (which is what php goes off of) not why we store it as signed :)

[Xenon]

Nope, int is just the type, but doesn't say anything about the length :)

in SQL INT is 32 Bit, in C++ it's 16 Bit, i believe Java uses nearly the same as C.
But as said, it depends on the programming language, the type int itself doesn't have a length (integer in haskell for example is a large as your memory size is ^^)

iirc those restrictions come from the first computers, where 16 Bit were long enough for everything, but as said int actually just means full numbers, no length, that's something added by coding languages.

well, why should we store it as unsigned, as it's not needed.
actually i cannot answer that question really, they decided once to store it signed, and that was it ;)

[Modin]

When a language says it supports a 32bit integer, it just means it's compiler is able to juggle the 32bit integer to make it work on the current architecure. It doesn't mean it's stored as a 32bit int in memory, just that it's able to juggle it to make it act like a 32bit int. Or like in haskell to give you the feel of a virtually unlimited size int. I think haskell will even use harddisk storage to store the int if it's larger than main memory.

Though, I agree...numbers don't have a finite length, just in computers they do because the hardware is finite :)

Anyways, I think we're both arguing the same side of the argument just from different angles :p

[Xenon]

Yes, we are both on the same argument. I just say, what you understand as integer depends on the base you are looking at it.

if you just have 8 bits, then an integer can just be 8 bit long ;)

But the term integer doesn't say anything about length at all, just about the type of a value stored at it ;)

[Modin]

exactly, case solved ;)

hmm... so what was the initial question of this thread? :p

[Xenon]

*gg*

i think the initial question was answered long time ago, was it? ^^

[SaN-DeeP]

0_o wat was the debate about.

i guess modin was right.

[Xenon]

The discussion was about terminology, and in the end we were right both, as it depends on your point of view what you assign to what term ;)

[Modin]

lol SaN-DeeP

[shawno]

Hi there,

I'm trying to convert a varchar type date that have stored in another mysql database into the integer format that vbulletin stores the date in. Any idea what query I would need to run in order to do this? I'm not a Mysql expert at all!

Thanks...

[Colin F]

Hi there,

I'm trying to convert a varchar type date that have stored in another mysql database into the integer format that vbulletin stores the date in. Any idea what query I would need to run in order to do this? I'm not a Mysql expert at all!

Thanks...
shawno,

I suggest you start a new thread with your problem, as it'll get more attention than an old, dug-up thread.

To your problem: you could probably do it with a small php script using something like strtotime (http://ch2.php.net/strtotime).