Recent

Author Topic: TSqlite3Dataset and Datetime column in TDBGrid  (Read 4343 times)

BlackGrizzly

  • Newbie
  • Posts: 2
TSqlite3Dataset and Datetime column in TDBGrid
« on: February 21, 2014, 11:19:52 am »
Hello! I'm using a TDBGrid with a TSQLite3Dataset and there is a datetime column. Nothing is showed in this column and the other columns of the TDBGrid have
content. Is there a way of show datetime values in TDBGrid column?

If use TSQLite3Connection + TSQLQuery datetime is showed in this column

FPC 2.6.2
Lazarus 1.2RC2

LacaK

  • Hero Member
  • *****
  • Posts: 600
Re: TSqlite3Dataset and Datetime column in TDBGrid
« Reply #1 on: February 21, 2014, 12:05:27 pm »
how do you have defined datetime column in your sqlite table ?
what values do you have in this column ?
(string: '2014-02-10' or some kind of real values?)

BlackGrizzly

  • Newbie
  • Posts: 2
Re: TSqlite3Dataset and Datetime column in TDBGrid
« Reply #2 on: February 21, 2014, 12:19:59 pm »
datetime column defined in SQLiteStudio as DATETIME
I use ( datetime ( 'now' , 'localtime' ) ) as default value for this column
well then datetime stored as string?
« Last Edit: February 21, 2014, 12:28:20 pm by BlackGrizzly »

kpeters58

  • Sr. Member
  • ****
  • Posts: 267
Re: TSqlite3Dataset and Datetime column in TDBGrid
« Reply #3 on: February 21, 2014, 06:59:20 pm »
You need to read up on SQLite storage classes/data types:

There is no date type - dates are usually represented as strings.

SQLite does not have a storage class set aside for storing dates and/or times. Instead, the built-in Date And Time Functions of SQLite are capable of storing dates and times as TEXT, REAL, or INTEGER values:
•TEXT as ISO8601 strings ("YYYY-MM-DD HH:MM:SS.SSS").
•REAL as Julian day numbers, the number of days since noon in Greenwich on November 24, 4714 B.C. according to the proleptic Gregorian calendar.
•INTEGER as Unix Time, the number of seconds since 1970-01-01 00:00:00 UTC.

Applications can chose to store dates and times in any of these formats and freely convert between formats using the built-in date and time functions.


The list below shows you all the available storage classes/data types:

NULL. The value is a NULL value.

INTEGER. The value is a signed integer, stored in 1, 2, 3, 4, 6, or 8 bytes depending on the magnitude of the value.

REAL. The value is a floating point value, stored as an 8-byte IEEE floating point number.

TEXT. The value is a text string, stored using the database encoding (UTF-8, UTF-16BE or UTF-16LE).

BLOB. The value is a blob of data, stored exactly as it was input.


« Last Edit: February 21, 2014, 07:06:54 pm by kpeters58 »
Lazarus 2.0.4/FPC 3.0.4/Win 64

LacaK

  • Hero Member
  • *****
  • Posts: 600
Re: TSqlite3Dataset and Datetime column in TDBGrid
« Reply #4 on: February 25, 2014, 07:42:00 am »
datetime column defined in SQLiteStudio as DATETIME
I use ( datetime ( 'now' , 'localtime' ) ) as default value for this column
well then datetime stored as string?
can you post here your SQLite database with one table with one datetime column with values, which are correctly displayed in SQLiteStudio, but not in Lazarus using TSQLite3Dataset

BigChimp

  • Hero Member
  • *****
  • Posts: 5740
  • Add to the wiki - it's free ;)
    • FPCUp, PaperTiger scanning and other open source projects
Re: TSqlite3Dataset and Datetime column in TDBGrid
« Reply #5 on: August 04, 2014, 09:17:46 am »
Sample application that demonstrates this (uses datetime fields in sqlite, see table tos starttime, finishtime) in bug http://bugs.freepascal.org/view.php?id=26551
Want quicker answers to your questions? Read http://wiki.lazarus.freepascal.org/Lazarus_Faq#What_is_the_correct_way_to_ask_questions_in_the_forum.3F

Open source including papertiger OCR/PDF scanning:
https://bitbucket.org/reiniero

Lazarus trunk+FPC trunk x86, Windows x64 unless otherwise specified

 

TinyPortal © 2005-2018