Default Timestamp

Date: March 13th 2016
Last updated: March 13th 2016

Adding a default timestamp can be done inside the SQL statement. Create a column as usual and call TIMESTAMP in place of another data type (e.g. INTEGER). Add additional parameters as necessary (e.g. DEFAULT, NOT NULL). As this example looks at default timestamps I have included DEFAULT CURRENT_TIMESTAMP.

In this example I am recycling an example used previously. For this reason the code starts by dropping a table (vet_visit). This table is then recreated to include the timestamp. The other tables include animals and patients which were created when I looked at foreign_keys.

import sqlite3

# connect
con = sqlite3.connect('gitbook.db')

# cursor
cur = con.cursor()

# drop vet_visit table
cur.execute('DROP TABLE IF EXISTS vet_visit')

# create visit table 
# Note line 3 which creates "sqltime" to set default timestamp
cur.execute('CREATE TABLE vet_visit (visitid INTEGER PRIMARY KEY AUTOINCREMENT,\
             visitpatientid INTEGER, visitanimalid INTEGER, visitprice REAL,\
             sqltime TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,\
             FOREIGN KEY(visitpatientid) REFERENCES patient(patientid),\
             FOREIGN KEY(visitanimalid) REFERENCES animals(animalid));')

# data
visits = [(1, 1, 35.00, ), \
          (1, 1, 35.00, ), \
          (2, 2, 43.00, ), \
          (1, 1, 126.00, ), \
          (2, 2, 43.00, ), \
          ]

# execute
cur.executemany('INSERT INTO vet_visit (visitpatientid, visitanimalid, \
                 visitprice) VALUES (?, ?, ?);', visits)

# commit and close
con.commit()
con.close()

GUI