Solved

# currency field in mysql

Posted on 2012-03-21
375 Views
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!
0
Question by:onyourmark
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points

LVL 2

Assisted Solution

n4th4nr1ch earned 167 total points
ID: 37746063
0

LVL 10

Assisted Solution

Tobias earned 166 total points
ID: 37746067
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

Author Comment

ID: 37746095
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

Author Comment

ID: 37746116
sorry, that did not make sense.
0

LVL 143

Accepted Solution

Guy Hengel [angelIII / a3] earned 167 total points
ID: 37747298
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

Author Closing Comment

ID: 37750322
Thanks all.
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
Containers like Docker and Rocket are getting more popular every day. In my conversations with customers, they consistently ask what containers are and how they can use them in their environment. If you’re as curious as most people, read on. . .
If you're a developer or IT admin, you’re probably tasked with managing multiple websites, servers, applications, and levels of security on a daily basis. While this can be extremely time consuming, it can also be frustrating when systems aren't wor…
There's a multitude of different network monitoring solutions out there, and you're probably wondering what makes NetCrunch so special. It's completely agentless, but does let you create an agent, if you desire. It offers powerful scalability …
###### Suggested Courses
Course of the Month2 days, 23 hours left to enroll