0

I have managed to complete the rest of the history part except the time of transaction.

I have tried several different ways to solve this problem, using data types like "DATETIME" or "TIMESTAMP", but the output is either "None", 0 or one of the following error messages (queries used are on top of the error message):

ALTER TABLE buy ADD date timestamp default(getdate());
  • Runtime error: Cannot add a column with non-constant default

ALTER TABLE buy ADD date datetime not null;
  • Runtime error: Cannot add a NOT NULL column with default value NULL

I have also tried to use "CURRENT_TIMESTAMP" but when manually updating it, all of the history of transactions were updated to the same time and date (neither of which corresponded to my time zone nor were correct).

All of the rows were created with the table except for date:

ALTER TABLE buy ADD date datetime;

This is my SQL table:

CREATE TABLE buy (
symbol varchar(255),
name varchar(255),
user_id int,
shares int,
price int, 
date datetime);

App.py:

@app.route("/history")
@login_required
def history():
    """Show history of transactions"""
            # Obtain database content
    histories = db.execute("SELECT * from buy WHERE user_id=:user_id", user_id=session["user_id"])

    return render_template("history.html", histories=histories)

HTML:

    <tbody>
        {% for history in histories %}
        <tr>
            <td class="text-start">{{ history.symbol.upper() }}</td>
            <td class="text-end">{{ history.shares }}</td>
            <td class="text-end">{{ history.price }}</td>
            <td class="text-end">{{ history.date }}</td>
        </tr>
        {% endfor %}
    </tbody>

The output: output cs50 finance history

5
  • sqlite does not have a datatime datatype, here is the datatype doc. The problem is likely caused by the insert to the buy table. Please post that code from the buy route presumably if you want further investigation. NB you will not be able to update the rows that already exist in buy table to the actual date/time of the transaction (if the data is not there, which seems likely). Sep 24, 2022 at 11:20
  • Thank you for your replay, but I don't fully understand what you meant by posting the buy route. Did you mean how I inserted the column, created the table or something of the like? Thank you in advance.
    – Eva
    Sep 24, 2022 at 14:32
  • how are rows inserted into the buy table? Please edit that info into the question. Sep 24, 2022 at 17:24
  • Done! Please let me know if the changes fulfil your comment.
    – Eva
    Sep 24, 2022 at 17:46
  • looks like i was trying to answer the wrong question. posted an answer, which i hope gets you where you need to be. Sep 24, 2022 at 18:57

2 Answers 2

1

I think you've conflated two different concepts here. First, understand that ALTER TABLE changes the entire table,not just a single row. Don't use that to update an individual record.

Next, you can add a column with the data type timestamp or datetime. (Sometimes, timestamp can give you issues with default values. If you can't fix that, use datetime, although I prefer timestamp.) Adding the column to the table (assuming the column doesn't already exist) is done with the ALTER TABLE statement. If you're doing stock transactions that's almost certainly what you want.

You can set the table up to automatically use the current timestamp to fill in the field when the update is made, but that has a potential problem. Do you want the timestamp to be updated if the row is ever updated or not? The ON UPDATE clause in the creation of the column controls this. If any field in the row is changed later, the timestamp or datetime field will be updated too. Well, unless you turn it off. (See link below) It's better to manually insert the timestamp when you insert the row. This guarantees that the original timestamp is never lost due to automatic processes. BUT, if you correctly set up the column in the table, it will work as intended, with the timestamp being entered when the row is created and not changed if the row is altered.

Assuming you're adding buy records to a table, you should use an insert (or update) statement to add the specific record. You can then include the current timestamp value. Or, you can set the column to insert the current timestamp when the row is inserted.

Here's some useful info. Read it. It'll explain all this in detail, with examples.

https://dev.mysql.com/doc/refman/8.0/en/timestamp-initialization.html

But, just in case you're really not making progress after all that, try this. DO THIS ONE TIME ONLY to set up the column in the table, not when inserting rows:

alter table buy drop date;
alter table buy add  date TIMESTAMP DEFAULT CURRENT_TIMESTAMP;

These two commands will first delete the date field/column from the table to clear the way. The second alter statement will add exactly what you're seeking.

One last note. timestamp and datetime are two different formats. Google them to understand the differences. Or just read this: https://www.eversql.com/mysql-datetime-vs-timestamp-column-types-which-one-i-should-use/

If this answers your question, please click on the check mark to accept. Let's keep up on forum maintenance. ;-)

4
  • This is sqlite not mysql. sqlite does not have a storage class for date/time data. Dates should be stored as TEXT, REAL, or INT as per the doc in order to use the date and time functions. Sep 26, 2022 at 11:01
  • There was some aparently some problem with the table it self. I deleted it completly and recreated implementing the date column as you said and it worked. Thank you very much.
    – Eva
    Sep 26, 2022 at 14:44
  • Hmmm... Eva,, did timestamp type work for you? Are you using sqlite or mysql?
    – Cliff B
    Sep 26, 2022 at 18:25
  • This is the column i ended up creating for time: time TIMESTAMP DEFAULT CURRENT_TIMESTAMP, I am using sqlite3.
    – Eva
    Oct 9, 2022 at 17:13
0

Sounds like the problem is updating the existing rows with some datetime.

I have also tried to use "CURRENT_TIMESTAMP" but when manually updating it, all of the history of transactions were updated to the same time and date (neither of which corresponded to my time zone nor were correct).

This is one good option. CURRENT_TIMESTAMP is in UTC. Some say "store in UTC, display in local". Does it really matter for this test data what the time zone is? If it does you could try to update the date column with

date(CURRENT_TIMESTAMP,'localtime')

All the rows will get the same date/time unless the seconds and/or minutes roll over while it's running. (it will run in micro-seconds so it is very unlikely).

If you want them to have different times, look at section 3. Modifiers of the sqlite Date and Time Functions doc.

Perhaps something like

update buy set date = datetime(date,'+'||(rowid*100)||' minutes')
2
  • If I ADD a column as you said date(CURRENT_TIMESTAMP,'localtime') I get the following error: Parse error: near "(": syntax error ALTER TABLE buy ADD date(CURRENT_TIMESTAMP, 'localtime');
    – Eva
    Sep 25, 2022 at 9:41
  • I have tried everything (I also updated it as you said) but the output is always "None". It looks like there is some kind of lack of connection between the database and the python code and html. Is their something "silly" that I am overlooking? Thank you for taking the time to help me out.
    – Eva
    Sep 25, 2022 at 9:46

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .