11

How do I use an SQL statement on an sqllite database to insert the current date in UTC. I found the NOW function but what format is that in? This will be on mobile devices so everyone will have a different locale, however, I need a standard time format because the device will compare the dates with my server.

Also, is there a way to automatically update a 'modified' field when the data in the row is changed like you can in MySQL?

teukkam
  • 4,267
  • 1
  • 26
  • 35
jax
  • 37,735
  • 57
  • 182
  • 278

2 Answers2

14

SELECT DATETIME('now') returns the current UTC datetime. See Date And Time Functions. You can use DATETIME DEFAULT CURRENT_TIMESTAMP with column declaration.

Format 11, the string 'now', is converted into the current date and time as obtained from the xCurrentTime method of the sqlite3_vfs object in use. Universal Coordinated Time (UTC) is used

For the 'modified' field you can use a trigger.

liggett78
  • 11,260
  • 2
  • 29
  • 29
  • how do I get it to print like this YYYY-MM-DDTHH:MM:SS – jax Nov 24 '10 at 11:57
  • http://stackoverflow.com/questions/4099862/convert-java-date-into-xml-date-format-and-vice-versa/4100363#4100363 – Kennet Nov 24 '10 at 12:07
  • 3
    @jax You can get your expected format using this format `'%Y-%m-%dT%H:%M:%S'` . Example `SELECT strftime('%Y-%m-%dT%H:%M:%S',DATETIME('now', 'utc'))` – Moshi Apr 03 '18 at 13:36
3

You don't specify what you use to develop your application on. I prefer using QDate::toJulianDay and QDate::fromJulianDay in Qt to store dates in an SQLite database as an integer if I only need to store the date.

teukkam
  • 4,267
  • 1
  • 26
  • 35