Article Index
SQLite Tutorial
Logging All Inserts, Updates, and Deletes
UTC and Localtime
The ATTACH Command
The Power of the Sign Function
Creating a Permanent Sign Function
Spreadsheet Format to Normalized Data
C and C API
A C Program -- Building a Class to Do the Work
Defining SQLite User Functions
Aggregate Functions
Reading Images (Blob data)

UTC and Localtime

Note that select DATETIME('NOW') returns UTC or Coordinated Universal Time, but select datetime('now','localtime') returns the current time.

sqlite> select datetime('now');
2004-10-18 23:32:34

sqlite> select datetime('now','localtime');
2004-10-18 19:32:46

There is an advantage to inserting UTC time like we did with the triggers above, since UTC can easily be converted to localtime after UTC has been entered in the table. See the command below. By inserting UTC, you avoid problems when working with multiple databases that may not share the same timezone and/or dst settings. By starting with UTC, you can always obtain the localtime. (Reference: Working with Time)

CONVERTING TO LOCALTIME:

sqlite> select datetime(timeEnter,'localtime') from exam;

Other Date and Time Commands

If you look in the sqlite3 source file "./src/date.c", you will see that datetime takes other options. For example, to get the localtime, plus 3.5 seconds, plus 10 minutes, you would execute the following command:

sqlite> select datetime('now','localtime','+3.5 seconds','+10 minutes');
2004-11-07 15:42:26

It is also possible to get the weekday where 0 = Sunday, 1 = Monday, 2 = Tuesday ... 6 = Saturday.

sqlite> select datetime('now','localtime','+3.5 seconds','weekday 2');
2004-11-09 15:36:51

The complete list of options, or modifiers as they are called in this file, are as follows:

NNN days
NNN hours
NNN minutes
NNN.NNNN seconds
NNN months
NNN years
start of month
start of year
start of week
start of day
weekday N
unixepoch
localtime
utc

In addition, there is the "strftime" function, which will take a timestring, and convert it to the specified format, with the modifications. Here is the format for this function:

** strftime( FORMAT, TIMESTRING, MOD, MOD, ...)
**
** Return a string described by FORMAT. Conversions as follows:
**
** %d day of month
** %f ** fractional seconds SS.SSS
** %H hour 00-24
** %j day of year 000-366
** %J ** Julian day number
** %m month 01-12
** %M minute 00-59
** %s seconds since 1970-01-01
** %S seconds 00-59
** %w day of week 0-6 sunday==0
** %W week of year 00-53
** %Y year 0000-9999

Below is an example.

sqlite> select strftime("%m-%d-%Y %H:%M:%S %s %w %W",'now','localtime');
11-07-2004 16:23:15 1099844595 0 44

<< Prev - Next >>