Page 1 of 1
storage of time in database
Posted: Wed Feb 08, 2006 1:16 pm
by ed209
feyd | This thread is referencing the following thread.
so would that mean have a last_login field (for example) as a 'varchar' in MySQL instead of a 'datetime' and using
instead of (what I was about to do before seeing this post) for a datetime ?
Posted: Wed Feb 08, 2006 1:21 pm
by feyd
use int(11), not varchar
Posted: Wed Feb 08, 2006 2:58 pm
by raghavan20
feyd wrote:use int(11), not varchar
I am curious to know why you are using UNIX timestamp instead of mysql to avoid unnecessary conversions in PHP files back again???
Posted: Wed Feb 08, 2006 7:00 pm
by feyd
read the referenced thread.
Posted: Wed Feb 08, 2006 7:41 pm
by raghavan20
I prefer binary timestamps i.e. int(11) .. basicaly stored unix-timestamp, versus the database's versions.. but I write for multiple architectures.. so...
But I could not make out much from it because I have not worked with different architectures.

Posted: Wed Feb 08, 2006 10:29 pm
by feyd
differing architectures store time in their own formats. When a selection is made, it often has to be displayed in a different format. To simplify the need to ask the database to convert it back to a usable format (especially when it needs to be used multiple times in a single page, for different purposes) becomes a pain. It's just easier to store it in a known, usable format, and start from there.
Posted: Thu Feb 09, 2006 4:34 am
by raghavan20
feyd wrote:differing architectures store time in their own formats. When a selection is made, it often has to be displayed in a different format. To simplify the need to ask the database to convert it back to a usable format (especially when it needs to be used multiple times in a single page, for different purposes) becomes a pain. It's just easier to store it in a known, usable format, and start from there.
you mean to say all architectures understand / use Unix format so when using PHP in different architectures you can use it directly without the need for conversion???
Posted: Thu Feb 09, 2006 4:59 am
by Weirdan
you mean to say all architectures understand / use Unix format so when using PHP in different architectures you can use it directly without the need for conversion???
Unix timestaps are plain integers... and yes, all widely used architectures are able to use integers

Posted: Thu Feb 09, 2006 7:34 am
by raghavan20
Weirdan wrote:you mean to say all architectures understand / use Unix format so when using PHP in different architectures you can use it directly without the need for conversion???
Unix timestaps are plain integers... and yes, all widely used architectures are able to use integers

what about the normal timestamp(14) format: YYYYMMDDHHMMSS ; they are also integers and they should also be read by all architectures...i am still confused...
Posted: Thu Feb 09, 2006 9:37 am
by feyd
- that's not an integer. It may look it, but it isn't.
- It will behave differently across architectures. For instance, in TransactSQL a timestamp is really a datetime, not the timestamp defined in the standard.