currency field in mysql

Can someone suggest what field is best to use and the best way to handle and store and create currencies fields. But there will be various currencies there (USD, JPY, etc.)

Also, is there some special way to handle GMT times?

Thanks!
onyourmarkAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Guy Hengel [angelIII / a3]Connect With a Mentor Billing EngineerCommented:
please make sure you don't mix up stuff.

30.3$  is 2 things, looking at it a normalized way:
* 30.3  amount, which is decimal
* $     currency, which can be stored in a dedicated field with either
    - USD  : it's iso code (recommended)
    - $  :  it's visual representation
    - xxx: some other lookup key value, for example a numerical key which is the fk for another table that stores the currencies used

if you stored litterally '30.3$' in a string/text field, you will end up with nothing but problems when you need to do SUM( ) or similar math stuff with the amount, or currency conversion(s) to other currencies.
0
 
TobiasConnect With a Mentor Commented:
Dear,

The for money I suggest decimal, I don't know the precision of the decimal that you want. Most of the time two decimals it's ok.

For GMT times, I would use timestamp that it's in GMT too.

Regards
0
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
onyourmarkAuthor Commented:
Would something like this work for price?

PriorOpen` decimal(45) NOT NULL DEFAULT ''

and how about for the GMT time:

`DateGMT` timestamp(45) NOT NULL DEFAULT '',
0
 
onyourmarkAuthor Commented:
sorry, that did not make sense.
0
 
onyourmarkAuthor Commented:
Thanks all.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.