Best way to store date and time in database

General discussions related to php

Moderators: egami, macek, gesf

Post Reply
tomwelch

Sun Mar 19, 2006 3:11 am

Hi guys

I want my users to be able to specify their chosen date format, ie dd/mm/yyyy or yyyy/mm/dd etc etc... But i obviously have to store it in the database all in the same format. I noticed the phpbb stores all its dates as unix time stamps is this the best way?

If so I can convert from a unit time stamp fine, and I can even convet yyyy/mm/dd to a unix time stamp so thats fine.

But then I though what happens if my user has their date as "Mon 13 Feb, 2004 10:31am"

Is there an easy of converting one format to another so I could simply say

Code: Select all

convert($current_format, $required_format)
Then I would be able to convert to us format every time and keep using unix time stamps.

Thanks Guys
Coditor
php-forum GURU
php-forum GURU
Posts: 243
Joined: Wed Feb 01, 2006 9:18 am
Location: Netherlands
Contact:

Sun Mar 19, 2006 1:40 pm

Unix timestamps are the best way of saving them. You can easilly convert them to readable text using the date or strftime functions. To convert a string back to a timestamp, have a look at the strtotime function. Not that it cannot handle every format properly, you may have to do some manual conversion via e.g. regular expressions.

Coditor
User avatar
gesf
Moderator
Moderator
Posts: 1716
Joined: Sun Dec 29, 2002 5:03 am
Location: Portugal / Sweden
Contact:

Sun Mar 19, 2006 3:29 pm

Like Coditor said... Unix Timestamp!
Also there's no need to use PHP for the date/time convertion.
We can easily do that in our MySQL query when retrieving the data.

Example 1:

Code: Select all

SELECT FROM_UNIXTIMESTAMP(datetime_field, '%Y-%M-%D') AS mydate FROM table;
Example 2:

Code: Select all

SELECT * FROM table WHERE FROM_UNIXTIMESTAMP(datetime_field, '%Y-%M-%D') = CURDATE();
Sincerely,
Gonçalo "gesf" Fontoura
Coditor
php-forum GURU
php-forum GURU
Posts: 243
Joined: Wed Feb 01, 2006 9:18 am
Location: Netherlands
Contact:

Mon Mar 20, 2006 12:57 am

True. I forgot to mention that because I usually read the Unix timestamp into PHP as well.
Post Reply