Re: Workings of Forum software

From: Lew Pitcher <lew.pitcher_at_digitalfreehold.ca>
Date: Mon, 22 Oct 2018 14:04:27 -0400
Message-ID: <pql3fe$r2e$1_at_dont-email.me>

mynameismisterbig_at_gmail.com wrote:

> ..So, I got around to wondering about how forum software works, cause I
> wanna make my own.
> [snip]
> Btw, I want to keep the date in dd/mm/yyyy format in the table

[Quoted] No, you don't want to do this. I'll explain below

> - how would
> I do this? And I also want to keep the time - hour:min:sec format.

[Quoted] [Quoted] Again, no, you don't want to do this.

> Will
> ORDER BY DATE DESC sort it correctly in this case? Or do I have to do
> something special?

[Quoted] [Quoted] First off, you should recognize that there are two issues here: 1) the storage of a date and time in the database, and 2) the presentation of the date and time to the user.

MySQL provides a number of column-types that can store date and time information. There is

  DATE	which just stores dates
		(such as <December 31, 1999>)
  DATETIME	which stores dates and times
		(such as <December 31, 1999 at 23:59>
  TIMESTAMP which, like DATETIME, also stores dates and times, but with
		different range criteria from DATETIME
  TIME	which stores times
		(such as <23:59>)
  YEAR	which stores year values
		(such as <1999>)

If your need is to store a co-ordinated date and time (such as the date and time that a post was submitted), then you want a DATETIME or TIMESTAMP column. If, however, you want separate dates and times (such as a birthdate, and (separately) an alarm clock time), you want two columns: one of DATE, and one of TIME.

Your SQL has facilities to extract stand-alone date information from a DATETIME or TIMESTAMP value. It can also extract stand-alone time information from a DATETIME or TIMESTAMP value. In other words, if (using a DATETIME or TIMESTAMP value), you can see all the posts that (no matter what the time) came in between December 1 and December 5, and, independantly, you can see all the posts that (no matter what the date) came in between 04:00 and 14:00.

As for how the date and time are /presented/ to the user, your SQL can format any of these datatypes to meet your requirements, no matter /what/ the internal representation of the date/time data is. In other words, you don't need to care about the internal format that MySQL stores date/time data in; you can /present/ it how you need.

HTH

-- 
Lew Pitcher
"In Skills, We Trust"

Received on Mon Oct 22 2018 - 20:04:27 CEST