Geek asks and answers

How to display the following in a “readable” datetime format?

Database : SQLite  
Column : SomeTable.Logged (DateTime)

Im using a component System.Data.SQLite. I save the datetime value in the column Loggedas ticks. e.g. using c #

DateTime.Now.Ticks;

An example value stored in SomeTable.Loggedis:

634399267463299880

How, using sql, would I display this in a “normal” date? for example '01-05-2011 13:45:22'?

I am aware of the page http://www.sqlite.org/lang_datefunc.html , but I cannot make things work the way I want.

+3
source share
1 answer

Try:

SELECT strftime('%Y-%m-%d %H:%M:%S',
                SomeTable.Logged/10000000 - 62135596800,
                'unixepoch')

where

 62135596800 = DateTime(1970, 1, 1, 0, 0, 0).Ticks/10000000
             = number of seconds elapsed from 01/01/0001 00:00:00 
                                         until 01/01/1970 00:00:00;
 => SomeTable.Logged/10000000 - 62135596800
             = number of seconds elapsed from 01/01/1970 00:00:00
                                         until your date

 => 'unixepoch' to convert it to date value
 => '%Y-%m-%d %H:%M:%S' to format

Example:

SELECT strftime('%Y-%m-%d %H:%M:%S',
                634398543220000000/10000000 - 62135596800,
                'unixepoch')

==> 2011-05-01 13:45:22
+20
source

More articles:


All Articles