what is realdate in SQL?

Question

I have some SQLite database in which one of the columns has data type as realdate and the column has value as 2453137.5

can anyone please comment on this?

any help is appreciated :)

Maybe this will help.. https://stackoverflow.com/questions/8417217/sqlite-custom-datatypes — VBoka, Nov 05 '19 at 06:50

score 3 · Accepted Answer · answered Nov 05 '19 at 06:59

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.

In your example you are using REAL datatype to store Dates. It will give the output which is not human readable.

For eg., If i'm storing current date and time

CREATE TABLE
IF NOT EXISTS DATEREAL (d1 real);

INSERT INTO DATEREAL (d1)
VALUES(julianday('now'));

SELECT * from DATEREAL;

Output : 2458792.7882345

You can read this using built-in date() and time() as shown below

SELECT
    date(d1),
    time(d1)
FROM
    datereal;

Output :

date(d1)    time(d1)
2019-11-05  06:55:03

Check demo here

score 0 · Answer 2 · answered Nov 05 '19 at 06:44

One of the powerful features of SQLite is allowing you to choose the storage type.
Real number has 2 advantages:

High precision regarding fraction seconds
Longest time range

I got this answer from a user named Zso.
Here's the link to the original post How do DATETIME values work in SQLite?.

Hope this might help you to understand better.

what is realdate in SQL?

2 Answers2