CODEWITHSUNDEEP
c++sqlitedatedatetime
Stan Zeez
Stan Zeez

Reputation: 1188

How parse datetime format from datebase?

The database (SQLite) has a field of type REAL with the values of the form (42153.659595). How to translate this value in the form "dd.MM.yy HH:mm:ss" if 42153.659595 = 29.05.2015 15:49:49 ?

Upvotes: 1

Views: 117

Answers (2)

Stan Zeez
Stan Zeez

Reputation: 1188

The correct value is achieved using the API/builtin SQL functions (42153.659595 - value from database):

SELECT datetime(julianday(42153.659595, 'localtime') + 2415018.29167) AS DT;

Output:

          DT       |
2015-05-29 15:49:49|

Constant 2415018.29167 was selected manually and query:

SELECT datetime (2415018.29167);

returns the current Greenwich Mean Time.

I work with a third-party application and documentation on the database is missing. Perhaps this strange decision, but it works. Thank you all for answers.

Upvotes: 0

sehe
sehe

Reputation: 393457

You can be explicit about what calendar system you require: http://www.sqlite.org/lang_datefunc.html

SELECT julianday('now') - julianday('1776-07-04');

In principle just don't "parse" (you mean: interpret raw representation). Use Sqlite API/builtin SQL functions to do it for you

In the interest of information:

  • The date and time functions use a subset of IS0-8601 date and time formats.
  • The datetime() function returns "YYYY-MM-DD HH:MM:SS". The julianday() function returns the Julian day - the number of days since noon in Greenwich on November 24, 4714 B.C. (Proleptic Gregorian calendar)
  • The date() function returns the date in this format: YYYY-MM-DD. The time() function returns the time as HH:MM:SS.

Upvotes: 1

Related Questions