FilesExpand file tree

 master

/

date_time_ops.py

Copy path

Blame

More file actions

Blame

More file actions

Latest commit

 

History

History

History

69 lines (56 loc) · 2.87 KB

 master

/

date_time_ops.py

Copy path

Top

File metadata and controls

• Code
• Blame

69 lines (56 loc) · 2.87 KB

Raw

Copy raw file

Download raw file

Open symbols panel

Edit and raw actions

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

37

38

39

40

41

42

43

44

45

46

47

48

49

50

51

52

53

54

55

56

57

58

59

60

61

62

63

64

65

66

67

68

69

# Sebastian Raschka, 03/2014

# Date and Time operations in sqlite3

import sqlite3

sqlite_file = 'my_first_db.sqlite' # name of the sqlite database file

table_name = 'my_table_3' # name of the table to be created

id_field = 'id' # name of the ID column

date_col = 'date' # name of the date column

time_col = 'time'# name of the time column

date_time_col = 'date_time' # name of the date & time column

field_type = 'TEXT' # column data type

# Connecting to the database file

conn = sqlite3.connect(sqlite_file)

c = conn.cursor()

# Creating a new SQLite table with 1 column

c.execute('CREATE TABLE {tn} ({fn} {ft} PRIMARY KEY)'\

.format(tn=table_name, fn=id_field, ft=field_type))

# 1) Adding a new column to save date insert a row with the current date

# in the following format: YYYY-MM-DD

# e.g., 2014-03-06

c.execute("ALTER TABLE {tn} ADD COLUMN '{cn}'"\

.format(tn=table_name, cn=date_col))

# insert a new row with the current date and time, e.g., 2014-03-06

c.execute("INSERT INTO {tn} ({idf}, {cn}) VALUES('some_id1', DATE('now'))"\

.format(tn=table_name, idf=id_field, cn=date_col))

# 2) Adding a new column to save date and time and update with the current time

# in the following format: HH:MM:SS

# e.g., 16:26:37

c.execute("ALTER TABLE {tn} ADD COLUMN '{cn}'"\

.format(tn=table_name, cn=time_col))

# update row for the new current date and time column, e.g., 2014-03-06 16:26:37

c.execute("UPDATE {tn} SET {cn}=TIME('now') WHERE {idf}='some_id1'"\

.format(tn=table_name, idf=id_field, cn=time_col))

# 3) Adding a new column to save date and time and update with current date-time

# in the following format: YYYY-MM-DD HH:MM:SS

# e.g., 2014-03-06 16:26:37

c.execute("ALTER TABLE {tn} ADD COLUMN '{cn}'"\

.format(tn=table_name, cn=date_time_col))

# update row for the new current date and time column, e.g., 2014-03-06 16:26:37

c.execute("UPDATE {tn} SET {cn}=(CURRENT_TIMESTAMP) WHERE {idf}='some_id1'"\

.format(tn=table_name, idf=id_field, cn=date_time_col))

# Database should now look like this:

# id date time date_time

# "some_id1" "2014-03-06" "16:42:30" "2014-03-06 16:42:30"

# 4) Retrieve all IDs of entries between 2 date_times

c.execute("SELECT {idf} FROM {tn} WHERE {cn} BETWEEN '2013-03-06 10:10:10' AND '2015-03-06 10:10:10'".\

format(idf=id_field, tn=table_name, cn=date_time_col))

all_date_times = c.fetchall()

print('4) all entries between ~2013 - 2015:', all_date_times)

# 5) Retrieve all IDs of entries between that are older than 1 day and 12 hrs

c.execute("SELECT {idf} FROM {tn} WHERE DATE('now') - {dc} >= 1 AND DATE('now') - {tc} >= 12".\

format(idf=id_field, tn=table_name, dc=date_col, tc=time_col))

all_1day12hrs_entries = c.fetchall()

print('5) entries older than 1 day:', all_1day12hrs_entries)

# Committing changes and closing the connection to the database file

conn.commit()

conn.close()