Support for DATETIME and TIMESTAMP columns about sqlpp11 HOT 19 CLOSED

Hi niXman,

Not yet. I am currently working on unions and with-statements. Date and time might be next, although I am not 100% sure how to represent those types in the C++ world. I might be using std::time_point or std::tm.

Once the choice is made, I think that writing the actual type classes is relatively simple, take a look at integral.h for instance. Contributions are welcome.

If you wonder why there is no support for these types yet: Well, I tend to avoid them and just store bigints for seconds since epoch. That's doing the job for me, but I understand that support for date/time is a requirement :-)

Best,

Roland

from sqlpp11.

I think the std::tm. std::chrono has no predefined types for day/month/year.

from sqlpp11.

Once the choice is made, I think that writing the actual type classes is relatively simple, take a look at integral.h for instance. Contributions are welcome.

I'll try to understand...

from sqlpp11.

Let me know if you get stuck :-)

from sqlpp11.

Hi,

I do not understand how to do this ... :(
You still have not found the time for implementing this?

from sqlpp11.

Hi,

No, I haven't found the time yet, but it is high on my todo list.

Best,

Roland

from sqlpp11.

Just so you know: I started working on this. You can see the first few steps here:

https://github.com/rbock/sqlpp11/tree/feature/date_time

from sqlpp11.

Oh, thank you!

from sqlpp11.

Admittedly, I am not making any progress here.

Seems to me that date and time data types, operators and functions are horribly different between database, e.g.

• MySQL has date, datetime, time, timestamp, year (year? really?)
• Postgresql has timestamp and time (both with or without timezone), as well as date and interval
• sqlite does not have any date/time storage type at all, just functions to convert to/from string/int/real

Or how about operations on date-time types? If you subtract a datetime from another

• on MySQL using subtime() you get a datetime or time, using datediff you get an int (the number of days)
• on Postgresql using operator-() you get a an interval (of course)

Quite annoying. Reminds me of why I am using bigint for timestamps...

Regards,

Roland

from sqlpp11.

Hi Roland
I can contribute date and date_time classes. I've written my own time-series database, but recently also created adaptors for postgresql and sqlite - so I know what you are going through. I'm afraid the only portable way to store date and date-time are as ISO strings. My classes support microsecond precision. I have very fast conversion routines to and from ISO. The only thing that isn't entirely done yet are the time zones. Let me know if you are interested.

Best Regards, Peter Ritter

from sqlpp11.

Hi Peter,

Such classes would be helpful, especially when working with sqlite which does not have any date/time column types afaict. And for most queries, ISO strings are just perfect. The fact that you could use such representations in a portable manner is one of the reasons I did not implement date/time column types in the first place. As I said, I am using bigint for both timestamps or dates. Works fine for me. But this kind of interpretation of numbers or strings is not part of the library, IMO, because sqlpp11 is about SQL, not about interpreting the data.

As it so happens, I started to think about dates and times again two days ago. I think I might just add a date and a datetime column type with almost no operations on it (operations are what eventually stopped me last time since they are just horribly different between the different databases).

I think it would be possible to offer the fundamentals in sqlpp11 and to flesh them out in the connector libraries, by use of support libraries like your date/time classes or directly in the user's code.

Best,

Roland

from sqlpp11.

Hi Roland
I think that if anyone creates an SQL table with a 'timestamp'/'datetime' column in it, then one should be able to query it using your library. It just seems like such a basic requirement. It's definitely a requirement for me.
SQlite does allow datetime columns. SQlite just allows you to submit datetime as either a string or a number - kind of weird - but I definitely use datetime columns in sqlite.
Anyway, let me know if you need anything in particular.
Peter

from sqlpp11.

Hi Roland
I would initially just focus on 'date' and 'datetime' / 'timestamp' with basic opertions like ==,<,>,<=,>= . That will solve most people's needs.
Peter

from sqlpp11.

Hi Peter,

Yes, that's what I am aiming for right now. Earlier, I wanted duration with +,-, too, but that is so weird...

Regarding sqlite and date/time, I read this:
https://www.sqlite.org/datatype3.html

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.

Thus, sqlite does not have date/time columns but functions to store/interpret data as such. That is my understanding, at least.

When you say, you are using datetime columns in sqlite, can you show me a sample table definition with such columns?

Thanks,

Roland

from sqlpp11.

Hi Roland

The library I am developing (TradingSystemAPI.com) relies on timeseries as
input. I have a native timeseries database that is part of my library, but
I also wrote an adaptor for SQLite, because it was a 'stepping stone' to
integrating with Postgresql, since both are SQL databases. I primarily use
Postgresql however, and I haven't touched SQlite in a while. However,
looking through my code, the timeseries tables are created like this:

create table my_table ( timestamp datetime not null primary key, ....

where the column is called 'timestamp' and the column type is 'datetime'.
My code reads and writes datetime values as ISO strings. I cannot entirely
remember if SQLite truncates the microseconds into milliseconds - that
could potentially happen. I vaguely remember an issue like that. In any
event, I read and write the same ISO format from Postgresql as from SQLite,
like "2014-10-12T13:50:10.112233" - although Postgresql does not use the
'T' by default. I think SQLite just stores the string representation, and
so it probably uses lexicographic comparison. But it does work.

Peter

On Tue, Oct 6, 2015 at 9:25 PM, Roland Bock [email protected]
wrote:

Hi Peter,

Yes, that's what I am aiming for right now. Earlier, I wanted duration
with +,-, too, but that is so weird...

Regarding sqlite and date/time, I read this:
https://www.sqlite.org/datatype3.html

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.

Thus, sqlite does not have date/time columns but functions to
store/interpret data as such. That is my understanding, at least.

When you say, you are using datetime columns in sqlite, can you show me a
sample table definition with such columns?

Thanks,

Roland

—
Reply to this email directly or view it on GitHub
#23 (comment).

from sqlpp11.

Hi,

I just pushed some code to develop which supports two new column types:

• day_point
• time_point

The former represents a date, the latter represents a date/time combination.

I chose these terms and not date and datetime or timestamp, since hell, all databases are different in the way they handle these things.

• MySQL has a date and a datetime and a timestamp, where the timestamp is stored in UTC internally, but is converted back to local time when retrieved (implications are beyond me, at the moment). Interestingly, datetime literals are need to be declared as timestamp. timestamp columns are auto-assigned. timestamp literals can have microseconds, but these are not stored. What a mess.
• Sqlite3 claims to have no date/time column types, but you can declare columns as date or datetime, no problem. Huh? You can assign dates to datetime columns and vice versa. So far, so good, but this can lead to datetime in a date column (i.e. you may have time of day data in the datecolumn). The syntax for date/datetime literals is different from other vendors, since these are actullay not literals but functions. And you need strftime to specify milliseconds. Again, what a mess.
• PostgreSQL seems to close to the SQL ISO standard. There is a date, and a timestamp column type, the latter can be with or without timezone. Granularity for timestamp is microseconds. This seems quite clean.
• Oracle's date type has a granularity of seconds. Huh? And a timestamp has a granularity of microseconds. Did not look any further...

Since the situation is quite chaotic, I decided to use type names that do not directly related to whatever your database vendor offers. Unless I am mistaken, day_point is equivalent to a date in PostgreSQL and time_point is equivalent to timestamp without time zone.

Other than the comparison operators (you can compare day_point with time_point), there is only the assignment operator.

colDayPoint = someDayPoint // OK
colDayPoint = someTimePoint // ERROR
colTimePoint = someTimePoint // OK
colTimePoint = someDayPoint // OK

You cannot insert a time_point into a day_point column and you cannot update a day_point column with a `time_point'. This is because some databases (e.g. sqlite3) do not perform reasonable narrowing in that case.

The C++ types representing day_point and time_point are std::chrono::time_point<std::chrono::system_clock, PERIOD> with periods of a day and a microsecond. The types are also known as sqlpp::chrono::day_point and sqlpp::chrono::mus_point.

The day_point is an alias of the day_point in Howard Hinnants date library, see https://github.com/HowardHinnant/date

This library is used for serialization and I heartily recommend it for any date/time operation. If you haven't seen the CppCon video, take a look, it is really, really cool: https://www.youtube.com/watch?v=tzyGjOm8AKo

As of now, the only connector that can be used with day_point and time_point is sqlite3. I will try to make the required adjustments for MySQL soon. It would be great if someone could work on PostgreSQL and provide a pull request for Matthijs (as mentioned elsewhere, there are minor changes in the connection class required as well, let me know, if you need help).

Please let me know what you think :-)

Best,

Roland

from sqlpp11.

AFAIK, you can declare columns as anything in sqlite, it's all just text.

Actually, there's a gotcha: if you have a real type name inside, it'll be used.
I.e. into column type is "int" for sqlite, since it saw "int" as part of the name.

from sqlpp11.

On 2015-10-29 18:25, ven wrote:

AFAIK, you can declare columns as anything in sqlite, it's all just
text.

Actually, there's a gotcha: if you have a real type name inside, it'll
be used.
I.e. |into| column type is "int" for sqlite, since it saw "int" as
part of the name.

—
Reply to this email directly or view it on GitHub
#23 (comment).

Thanks for the hint. I am not sure if I consider that better or worse ;-)

from sqlpp11.

I am closing this issue with release 0.35, which brings day_point and time_point which correspond to date and datetime.

sqlpp11 uses @HowardHinnant 's date library to represent and serialize date and datetime values. Their C++ representations are date::day_point and sqlpp::microsecond_point.

Using the date library and std::chrono, you should be able to convert to and from whatever other format you use in your project with ease.

Note that there are almost no functions related to date and datetime in sqlpp11. As I see it today, the differences between the vendors are so big that it does not make sense to try to come up with a generic set of functions. It would add more confusion than benefits. I would therefore prefer to add date/time functions to the individual connector libraries.

You can try to convince me otherwise, of course :-)

from sqlpp11.