Solved

currency field in mysql

Posted on 2012-03-21
6
374 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
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

 

Author Comment

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

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

Forrester Webinar: xMatters Delivers 261% ROI

Guest speaker Dean Davison, Forrester Principal Consultant, explains how a Fortune 500 communication company using xMatters found these results: Achieved a 261% ROI, Experienced $753,280 in net present value benefits over 3 years and Reduced MTTR by 91% for tier 1 incidents.

Question has a verified solution.

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

Foreword This article was written many years ago, in the days when PHP supported the MySQL extension (http://php.net/manual/en/function.mysql-connect.php).  Today (http://php.net/manual/en/migration70.removed-exts-sapis.php) you would not use MySQL…
Introduction Since I wrote the original article about Handling Date and Time in PHP and MySQL (http://www.experts-exchange.com/articles/201/Handling-Date-and-Time-in-PHP-and-MySQL.html) several years ago, it seemed like now was a good time to updat…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

726 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