4

I am a php/mysql novice so I have been inserting dates in whatever format I usually display dates on my pages, like:

Saturday, June 19, 2010

directly into the database in plain text. But I have seen people enter it in some other way and then display that same record in various formats using some php function. What is the way to do this, so that I can have one record in the database and show it in various formats like:

6/19/2010
June 19, 2010
Saturday, June 19, 2010

etc...

2nd part of the question

How to do the same thing but with money?

Damon
  • 41
  • 2
  • 1
    These related php/mysql date questions could helpful http://stackoverflow.com/questions/409286/datetime-vs-timestamp http://stackoverflow.com/questions/977967/mysql-date-or-php-time – Mike B Jun 20 '10 at 01:01

3 Answers3

1

You should store the data as a MYSQL datetime feild. Then use php to format the data how you want it.

As for price i recomend you store it as US cents. That way you can convert it to other currencies very easily.

thomasfedb
  • 5,990
  • 2
  • 37
  • 65
  • If you need to make calculations with your dates at a later point you definitely should use `DATETIME`. (You can convert unix timestamps to datetime and vice versa but why not use the native type for it from the beginning?) – Till Theis Jun 20 '10 at 01:24
1

I myself store it as a unix timestamp.

The function time() returns a unix timestamp.

And checkout the date() function here:

http://php.net/manual/en/function.date.php

It takes two arguments: the format you want it displayed in and the unix timestamp.

Matthew
  • 15,282
  • 27
  • 88
  • 123
1

How to deal, store, change dates php + mysql


For dates just save them as a DATETIME. It's easy to read and easy to work with from within PHP. You could alternatively go with a pure TIMESTAMP but I would not recommend it.

DATETIME's range...
'1000-01-01 00:00:00' to '9999-12-31 23:59:59'.

TIMESTAMP's range...
'1970-01-01 00:00:01' UTC to '2038-01-19 03:14:07'

Not only that but you also have a lot of build-in MYSQL functions to work with DATETIME's like INTERVAL which will allow you to do great things.

// easy pull all comments values from within a range
SELECT * FROM your_table
WHERE your_date_field = DATE_SUB('2001-01-01 12:12:12' + INTERVAL 15 DAYS;

If you do need to work with timestamps on PHP you can pull them real time on SQL like this:

// converts a DATETIME field real time to timestamps
SELECT UNIX_TIMESTAMP(your_date_field)
FROM your_table (...)

And when you pull them out just format them anyway you like using strftime. Example bellow.

// pull a date from MySQL and format them to a custom format
// %A -> full weekday | %B -> full month name | %d -> month day | %Y - year 1999
$sql = "SELECT title, name, UNIX_TIMESTAMP(date) as timestamp
        FROM your_table
        WHERE your_limits";
$result = mysql_fetch_assoc(mysql_query($sql));
echo strftime("%A, %B %d, %Y", $result['timestamp']);


How to store currencies in MySQL


I would always store them in their uploaded value. Only when there is a transaction or a change should you convert from one currency to the other. Else keep the values as they were uploaded in their original price. 
| 135.23 | USD |
| 200.35 | EUR |
|  34.00 | GBP |

When displaying the currencies you can make the conversion real time. Just keep a separate table with the current exchange date and do the math real time. It would also be a plus to show the user your conversion rates.

Say the user above with the above account wanted to get his currency in EUR.

135.23 USD = 109.39 EUR (1 USD = 0.80893059 EUR)
    30 GBP =  35.90 EUR (1 GBP = 1.19681281 EUR)
             200.35 EUR
-----------------------       
       total 345.64 EUR
Frankie
  • 24,627
  • 10
  • 79
  • 121