Create day, date, time in DB - Question.

Question

Create_date -> Assume it will record as 5/2/2009. For search purposes when searching create date can we search by individual month or year from this or do we need to even record a create_day, create_month, crate_year for this? Once of the search filters for user content will be like example -> "Show content from last 2 weeks, last month, last year, current month only"

create_day -> While we record numeric date, many times for we may need to display the text day (ie: Thursday) to show when the object was created. Like we see on social websites "created on Thursday, Jan 29, 2009 at 3:45pm". To get this output, do we need to record a create_day for all objects / activities or is it calculated on each page load at application level?

create_time -> What time are we storing in DB? User local time or a fixed time? This is a global website. If fixed then let's say I do GMT by default. Then next question is how to display the correct time to a user so it matches his local time? Must factor into calculation that in US time changes twice a year (day light savings). OR, maybe record time always in GMT but display it to users based on their detected time zone,but then that means calculating time from timezone on each page load?

And since I am here, a side Q-> Any difference between lookup and reference table? How to distinguish these two tables -> "Account_status" which has values Active, Confirmed, etc... AND another table "City" which has city names. First table is a system ID table used in back end only. The city table is ID table used by users to select city from. Are these both lookup or reference or same/different?

Answer 1

• Create_date You'll usually want to use the DMBS underlying date storage type which stores all pieces of a date (and time if need be, like MySQL datetime). This opens up the ability to use date processing functions within the DBMS. For the filter example, you would calculate what the date was at 2 weeks ago. You may also get have a function to specify such a string in the DBMS. PHP's strotime() allows this.
• Create_day The text output is either automatic (MySQL datetime for instance) or it's easy to render whichever piece you need. (PHP date() can do this). It's generally a Good Idea to store a time stamp (datetime) for every record.
• Create_time If it's a global app you'll want to use UTC/GMT. Every user will have their offset applied to the times and as well as their form submissions (such as searching). PHP's DateTime objects allow specifying a timezone name as the offset. The user can choose from a list but in it's standard form that's a big list and isn't much towards usability if it doesn't even match their city (http://us.php.net/manual/en/timezones.php). The other option is getting the user's computer's timezone via Javascript.
• Lookup Table Yes those are both lookup tables.