Re: Workings of Forum software

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

Lew Pitcher wrote:

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

[Quoted] Oops... made a mistake in that format, it should be '%h:%i %p'

>  +---------------------+------------+------------+----------+----------+
>  | 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  mysql> select now() as Now, date(now()) as ISODate,

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

 | 2018-10-22 14:21:50 | 2018-10-22 | 22/10/2018 | 14:21:50 | 02:21 PM |

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

 1 row in set (0.00 sec)

-- 
Lew Pitcher
"In Skills, We Trust"

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