Under MySQL, Instant Messages, what does each number mean for the time?

[Merco]

Im looking at the instant messages which I've recieved using the mySQL thing, but the time it was sent is just 10 digits. What does each digit mean?
Thanks, im just curious.

-Richard

[mediman]

this digits are the seconds gone since 1.1.1970

[Greg Robson]

FYI: After my database analysis they should be DATETIME in future version (1/2 the space required)

[Merco]

That should save quite a bit of space, especially if there is a lot of messages and not a lot of web space!

-Richard

[Merco]

So for the 1.1.2002 the calculation should be:

60x60x60x24x7x52x32

So thats second x minutes x hours x hours in a day x week x weeks in a year x 32 years

But that gives around 60383232000, but MySQL reads around 101923000, which is only around 3.2 years ago.

Am I doing something wrong?

-Richard

[Greg Robson]

* Greg reaches to get his bible off the shelf:- PHP and MySQL Web Development, he also reaches for his printout of the database schema he's working on.

Right then...
Currently the IM time is stored as a bit integer (8 bytes - when unsigned maximum is 2⁶³-1 or 9,223,372,036,854,775,807 which when divided by the seconds in a year (31,557,600) gives you
292,271,023,045.31 years of possible dates - now as you can see the planet will most likely be destroyed along with the universe as we know it so...

...we use DATETIME, which uses 4 bytes and gives us until 9999-12-31 23:59:59 (still plenty of time and a little faster)

And I've been doing this with loads of fields for instance: we don't expect 4,200 million people to use YaBB so a mediumint will do (max 16,777,215), and so on and so on, with all the fields until they are all their proper size

And so ends today's lesson on relational database design.
* Greg closes his bible

[Merco]

Right....
So how can I work out a time then  ?
If I was to make one up?

[Joseph Fung]

Greg - the limitation with using datetime fields however, is that you need to then convert them to unix time stamps to do any calculations or display functionality on them.

It was decided VERY early on (the first fricking week of development   ) of YSE to use unix timestamps instead.

[Greg Robson]

In that case I'll see if we can use a smaller numeric field, I'm sure most things won't be around in 292,271 million years!