Re: Workings of Forum software

From: Lew Pitcher <lew.pitcher_at_digitalfreehold.ca>
Date: Mon, 22 Oct 2018 14:20:54 -0400
Message-ID: <pql4e6$sc$1_at_dont-email.me>

Lew Pitcher wrote:

> 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

>
> 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.

>
> 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?

>
> 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.

For example:
 mysql> select now() as Now, date(now()) as ISODate,

        date_format(now(),'%d/%m/%Y') as USDate, time(now()) as ISOTime,
        date_format(now(),'%h:%s %p') as USTime;
 +---------------------+------------+------------+----------+----------+
 | Now                 | ISODate    | USDate     | ISOTime  | USTime   |
 +---------------------+------------+------------+----------+----------+

 | 2018-10-22 14:20:08 | 2018-10-22 | 22/10/2018 | 14:20:08 | 02:08 PM |

 +---------------------+------------+------------+----------+----------+

 1 row in set (0.00 sec)

HTH

-- 
Lew Pitcher
"In Skills, We Trust"

Received on Mon Oct 22 2018 - 20:20:54 CEST