Transactions table missing a column

[ChuksJoy]

My transactions table is missing the txn_date column, I am using the schema provided

[fortunewalla]

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.

[fortunewalla]

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.

1. Create a new column txn_date of type DATE.

ALTER TABLE transactions
ADD COLUMN txn_date DATE AFTER ticker;

2. Copy over the date part of txn_time to txn_date

UPDATE transactions txn_date
SET txn_date = date(txn_time);

3. 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)