Menu
Database : SQLite
Column : SomeTable.Logged (DateTime)
Im using the System.Data.SQLite component. I save a datetime value in the Logged column as ticks. e.g. using c#
DateTime.Now.Ticks;
An example value that is saved in SomeTable.Logged is:
634399267463299880
How, using sql, would I display this in a 'normal' date? e.g. '01-05-2011 13:45:22'?
I'm aware of the page http://www.sqlite.org/lang_datefunc.html , but I can't quite get things to work the way I want.
813
dd/MM/yyyy — Example: 23/06/2013. yyyy/M/d — Example: 2013/6/23. yyyy-MM-dd — Example: 2013-06-23.
Overview of date and time formats The time is displayed as, hh:mm:ss AM/PM, where hh is the hour, mm is minutes, and ss is seconds.
On the Home tab, in the Number group, click the Dialog Box Launcher next to Number. You can also press CTRL+1 to open the Format Cells dialog box. In the Category list, click Date or Time. In the Type list, click the date or time format that you want to use.
Use datetime. strftime(format) to convert a datetime object into a string as per the corresponding format . The format codes are standard directives for mentioning in which format you want to represent datetime. For example, the %d-%m-%Y %H:%M:%S codes convert date to dd-mm-yyyy hh:mm:ss format.
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
