sqlite3 timestamp, sqlite3 datetime, sqlite3 epochtime

Test file

/Users/aaa/myfile/bitbucket/database/epochtime.sql

Sqlite3 timestamp, Sqlite3 epoach time, source file

  -- Save file to /tmp/test.sql
  -- source db.sqlite
  -- sqlite3 trydb.db < /tmp/test.sql
  -- epoachtime
		   
  CREATE TABLE tb1 (
  entry_id INTEGER PRIMARY KEY AUTOINCREMENT,
  create_date TEXT DEFAULT (datetime()),
  edit_date TEXT,
  addedtime DATETIME DEFAULT CURRENT_TIMESTAMP,
  epochtime DATETIME DEFAULT (strftime('%s', 'now')),
  entry_text TEXT NOT NULL
);
insert into tb1 (edit_date, entry_text) VALUES('333', 'entry_text')

Creat Table with epoch time

CREATE TABLE CodeBlock (
    id INTEGER PRIMARY KEY AUTOINCREMENT, 
    header TEXT, 
    codeblock TEXT, 
    addedtime DATETIME DEFAULT (stftime('%s', 'now')), 
    score INTEGER DEFAULT 0
);

-- The following DOES NOT WORK 
-- addedtime DATETIME DEFAULT stftime('%s', 'now'),
-- Need brackets  (..)

Source file from command line

  -- $b/database/epochtime.sql
  sqlite3 trdb.db < epochtime.sql
  sqlite3 trdb.db
  select * from tb1;

Copy data from one table to other table

  We have two tables with different columns: CodeBlock and oldtable
  and we want to copy data from CodeBlock_copy to CodeBlock

  -- CodeBlock table
  CREATE TABLE CodeBlock (
    id INTEGER PRIMARY KEY AUTOINCREMENT, 
    header TEXT, 
    codeblock TEXT, 
    addedtime DATETIME DEFAULT (strftime('%s', 'now')), 
    score INTEGER DEFAULT 0
);

  CodeBlock_copy has different column from CodeBlock

  CREATE TABLE CodeBlock_copy (
    id INTEGER PRIMARY KEY AUTOINCREMENT, 
    header TEXT, 
    codeblock TEXT
);

Use the following command to copy data CodeBlock_copy => CodeBlock

  INSERT INTO CodeBlock (header, codeblock) SELECT header, codeblock FROM CodeBlock_copy