storage of time in database

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
User avatar
ed209
Forum Contributor
Posts: 153
Joined: Thu May 12, 2005 5:06 am
Location: UK

storage of time in database

Post 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

Code: Select all

time();
instead of (what I was about to do before seeing this post) for a datetime ?

Code: Select all

date("Y-m-d H:i:s");
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

use int(11), not varchar
User avatar
raghavan20
DevNet Resident
Posts: 1451
Joined: Sat Jun 11, 2005 6:57 am
Location: London, UK
Contact:

Post 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???
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

read the referenced thread.
User avatar
raghavan20
DevNet Resident
Posts: 1451
Joined: Sat Jun 11, 2005 6:57 am
Location: London, UK
Contact:

Post 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. :roll:
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post 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.
User avatar
raghavan20
DevNet Resident
Posts: 1451
Joined: Sat Jun 11, 2005 6:57 am
Location: London, UK
Contact:

Post 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???
User avatar
Weirdan
Moderator
Posts: 5978
Joined: Mon Nov 03, 2003 6:13 pm
Location: Odessa, Ukraine

Post 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 :)
User avatar
raghavan20
DevNet Resident
Posts: 1451
Joined: Sat Jun 11, 2005 6:57 am
Location: London, UK
Contact:

Post 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...
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

  1. that's not an integer. It may look it, but it isn't.
  2. It will behave differently across architectures. For instance, in TransactSQL a timestamp is really a datetime, not the timestamp defined in the standard.
Post Reply