Solved

currency field in mysql

Posted on 2012-03-21
6
370 Views
Last Modified: 2012-03-21
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
Comment
Question by:onyourmark
6 Comments
 
LVL 2

Assisted Solution

by:n4th4nr1ch
n4th4nr1ch earned 167 total points
ID: 37746063
0
 
LVL 10

Assisted Solution

by:MadShiva
MadShiva 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

by:onyourmark
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
Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

 

Author Comment

by:onyourmark
ID: 37746116
sorry, that did not make sense.
0
 
LVL 142

Accepted Solution

by:
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

by:onyourmark
ID: 37750322
Thanks all.
0

Featured Post

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
mySQL 5.7 table repair needed. 4 50
Clean text to insert in database 9 52
simple mysql statement 3 32
How to loop bootstrap columns which contain database records 9 23
Introduction In this installment of my SQL tidbits, I will be looking at parsing Extensible Markup Language (XML) directly passed as string parameters to MySQL 5.1.5 or higher. These would be instances where LOAD_FILE (http://dev.mysql.com/doc/refm…
More Fun with XML and MySQL – Parsing Delimited String with a Single SQL Statement Are you ready for another of my SQL tidbits?  Hopefully so, as in this adventure, I will be covering a topic that comes up a lot which is parsing a comma (or other…
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
Internet Business Fax to Email Made Easy - With  eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, f…

867 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now