Comments (2)
My transactions table is missing the txn_date column, I am using the schema provided
Are you using the MySQL version? It is missing in the MySQL but is present in the PostgreSQL version.
MySQL
INSERT INTO trading.transactions
(`txn_id`, `member_id`, `ticker`, `txn_time`, `txn_type`, `quantity`, `percentage_fee`)
VALUES
('1', '45c48c', 'ETH', '2017-01-01 06:22:20.202995', 'SELL', '8.84298701787532', '0.12');
PostgreSQL
INSERT INTO trading.transactions
("txn_id", "member_id", "ticker", "txn_date", "txn_type", "quantity", "percentage_fee", "txn_time")
VALUES
('1', 'c81e72', 'BTC', '2017-01-01', 'BUY', '50', '0.30', '2017-01-01 00:00:00');
I think the quick fix is to create a new column txn_date
and extract the date from the txn_time
.
from sql-masterclass.
My transactions table is missing the txn_date column, I am using the schema provided
For MySQL, you can execute the following statements to get the job done.
- Create a new column
txn_date
of type DATE.
ALTER TABLE transactions
ADD COLUMN txn_date DATE AFTER ticker;
- Copy over the date part of
txn_time
totxn_date
UPDATE transactions txn_date
SET txn_date = date(txn_time);
- Check if the transaction is done.
mysql> describe transactions;
+----------------+------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------------+------------+------+-----+---------+-------+
| txn_id | int | YES | | NULL | |
| member_id | varchar(6) | YES | | NULL | |
| ticker | varchar(3) | YES | | NULL | |
| txn_date | date | YES | | NULL | |
| txn_time | timestamp | YES | | NULL | |
| txn_type | varchar(4) | YES | | NULL | |
| quantity | float | YES | | NULL | |
| percentage_fee | float | YES | | NULL | |
+----------------+------------+------+-----+---------+-------+
8 rows in set (0.00 sec)
mysql> select txn_id from transactions where txn_date is null;
Empty set (0.03 sec)
mysql> select txn_id,txn_date,txn_time from transactions limit 20;
+--------+------------+---------------------+
| txn_id | txn_date | txn_time |
+--------+------------+---------------------+
| 1 | 2017-01-01 | 2017-01-01 06:22:20 |
| 2 | 2017-01-01 | 2017-01-01 06:40:49 |
| 3 | 2017-01-01 | 2017-01-01 07:13:52 |
| 4 | 2017-01-01 | 2017-01-01 10:04:32 |
| 5 | 2017-01-01 | 2017-01-01 11:00:14 |
| 6 | 2017-01-01 | 2017-01-01 12:03:33 |
| 7 | 2017-01-01 | 2017-01-01 13:23:06 |
| 8 | 2017-01-01 | 2017-01-01 16:15:42 |
| 9 | 2017-01-01 | 2017-01-01 16:23:17 |
| 10 | 2017-01-01 | 2017-01-01 17:39:11 |
| 11 | 2017-01-01 | 2017-01-01 22:08:30 |
| 12 | 2017-01-01 | 2017-01-01 22:19:47 |
| 13 | 2017-01-01 | 2017-01-01 22:44:57 |
| 14 | 2017-01-02 | 2017-01-02 00:36:35 |
| 15 | 2017-01-02 | 2017-01-02 01:32:37 |
| 16 | 2017-01-02 | 2017-01-02 04:48:50 |
| 17 | 2017-01-02 | 2017-01-02 05:47:53 |
| 18 | 2017-01-02 | 2017-01-02 08:23:56 |
| 19 | 2017-01-02 | 2017-01-02 08:36:55 |
| 20 | 2017-01-02 | 2017-01-02 09:55:27 |
+--------+------------+---------------------+
20 rows in set (0.00 sec)
from sql-masterclass.
Related Issues (13)
- Extra Brackets HOT 1
- Filter for ETH ticker missing in step 4, Q4 HOT 1
- Wrong Answer for Step-2 (Question-6) HOT 1
- Error While Loading the data set to MY SQL Workbench HOT 3
- Filter for ETH ticker missing in Step 3 - Daily Prices Question 6 HOT 1
- Wrong solution for Question 6 in step4.md HOT 1
- GUI is not opening HOT 5
- Step 4 Question 3 wrong answer HOT 2
- Question 3,4, and 5 Errors HOT 1
- Step 3. Question 6. Answer is not considering 'ETH' filter HOT 1
- Step 4. Question 3. There are two Question 3. HOT 1
- Sql course HOT 2
Recommend Projects
-
React
A declarative, efficient, and flexible JavaScript library for building user interfaces.
-
Vue.js
🖖 Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
-
Typescript
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
-
TensorFlow
An Open Source Machine Learning Framework for Everyone
-
Django
The Web framework for perfectionists with deadlines.
-
Laravel
A PHP framework for web artisans
-
D3
Bring data to life with SVG, Canvas and HTML. 📊📈🎉
-
Recommend Topics
-
javascript
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
-
web
Some thing interesting about web. New door for the world.
-
server
A server is a program made to process requests and deliver data to clients.
-
Machine learning
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
-
Visualization
Some thing interesting about visualization, use data art
-
Game
Some thing interesting about game, make everyone happy.
Recommend Org
-
Facebook
We are working to build community through open source technology. NB: members must have two-factor auth.
-
Microsoft
Open source projects and samples from Microsoft.
-
Google
Google ❤️ Open Source for everyone.
-
Alibaba
Alibaba Open Source for everyone
-
D3
Data-Driven Documents codes.
-
Tencent
China tencent open source team.
from sql-masterclass.