[reposted to comp.unix.programmer; mistakenly posted to comp.databases.mysql]

Hi, Guys

I'm converting some code that uses the Mysql/Mariadb C api from
text queries to prepared statements, and would like my public
interfaces to use struct tm (Unix "broken-down" time) instead of
MYSQL_TIME.

ISTM that in all the years of the Mysql C API, /someone/ probably
has tackled such an interface. So, instead of "re-inventing the
wheel", I thought that I'd ask around: can you direct me to
or provide the source code for a proper MYSQL_TIME to Unix struct tm
conversion routine?

FWIW, for testing purposes, I wrote my own naive conversion
functions: timeMtoU() (which converts MYSQL_TIME to struct tm),
and timeUtoM() (which converts struct tm to MYSQL_TIME)

I include the source code for these two functions, in case
someone can suggest improvements or bugfixes.

struct tm *timeMtoU(MYSQL_TIME *mtime, struct tm *utime)
{
/*
** Note:
** MYSQL_TIME year is always a positive value
** ranging from year 1000AD to year 9999AD, or
** year 0000 for special cases.
**
** MYSQL_TIME month ranges from 1 (January) to 12 (December),
** while struct tm tm_mon ranges from 0 (January) to 11 (December)
** Subtract 1 from month to get tm_mon
*/
memset(utime,0,sizeof *utime);

utime->tm_sec = mtime->second;
utime->tm_min = mtime->minute;
utime->tm_hour = mtime->hour;
utime->tm_mday = mtime->day;
utime->tm_mon = mtime->month - 1;
utime->tm_year = mtime->year - 1900;

utime->tm_isdst = -1; /* let time functions figure it out */

/*
** NB: we naively leave tm_wday and tm_yday set to 0.
** If the caller /requires/ a valid tm_wday and/or tm_yday
** it's going to have to manipulate the struct tm itself
*/

return utime;
}

MYSQL_TIME *timeUtoM(struct tm *utime, MYSQL_TIME *mtime)
{
/*
** NOTE:
** struct tm tm_year is number of years (+ve or -ve) from
** the year 1900. So, the year 1899 is tm_year = -1,
** and year 1901 is tm_year = 1.
**
** struct tm tm_mon ranges from 0 (January) to 11 (December)
** while MYSQL_TIME month ranges from 1 (January) to 12 (December)
** Add 1 to tm_mon to get month
*/
memset(mtime,0,sizeof *mtime);

mtime->year = 1900 + utime->tm_year; /* error before 1000AD */
mtime->month = utime->tm_mon + 1;
mtime->day = utime->tm_mday;
mtime->hour = utime->tm_hour;
mtime->minute = utime->tm_min;
mtime->second = utime->tm_sec;
mtime->neg = 0; /* never negative time */
mtime->second_part = 0;

return mtime;
}

Thanks in advance

--
Lew Pitcher
"In Skills We Trust"

On Fri, 15 Mar 2024 19:18:51 -0000 (UTC), Lew Pitcher wrote:

> I'm converting some code that uses the Mysql/Mariadb C api from text
> queries to prepared statements, and would like my public interfaces to
> use struct tm (Unix "broken-down" time) instead of MYSQL_TIME.

Never found much need for DBMS-specific date/time conversion routines.
Those sorts of things belong in the application logic, not in the
database.

In article <ut2e0k$2fd1e$8@dont-email.me>,
Lawrence D'Oliveiro <ldo@nz.invalid> wrote:
>
>Never found much need for DBMS-specific date/time conversion routines.
>Those sorts of things belong in the application logic, not in the
>database.

What a totally pointless response.

--
If Jeb is Charlie Brown kicking a football-pulled-away, Mitt is a '50s
housewife with a black eye who insists to her friends the roast wasn't
dry.

On 2024-03-15, Kenny McCormack <gazelle@shell.xmission.com> wrote:
> In article <ut2e0k$2fd1e$8@dont-email.me>,
> Lawrence D'Oliveiro <ldo@nz.invalid> wrote:
>>
>>Never found much need for DBMS-specific date/time conversion routines.
>>Those sorts of things belong in the application logic, not in the
>>database.
>
> What a totally pointless response.

Luckily for me, the bot's operator seems to have put me in its killfile.

--
TXR Programming Language: http://nongnu.org/txr
Cygnal: Cygwin Native Application Library: http://kylheku.com/cygnal
Mastodon: @Kazinator@mstdn.ca

On Fri, 15 Mar 2024 21:19:16 -0000 (UTC)
Lawrence D'Oliveiro <ldo@nz.invalid> wrote:
>On Fri, 15 Mar 2024 19:18:51 -0000 (UTC), Lew Pitcher wrote:
>
>> I'm converting some code that uses the Mysql/Mariadb C api from text
>> queries to prepared statements, and would like my public interfaces to
>> use struct tm (Unix "broken-down" time) instead of MYSQL_TIME.
>
>Never found much need for DBMS-specific date/time conversion routines.
>Those sorts of things belong in the application logic, not in the
>database.

I assume you've not done my work with or on DBs. A lot of application logic
not to mention triggers are in the database itself in the form of PL/SQL, t-SQl
(or whatever procedural extension of SQL the DB supports) procedures/functions.
A lack of datetime functionality would be a show stopper in a lot of cases.

In article <ut3rse$2ri7l$1@dont-email.me>, <Muttley@dastardlyhq.com> wrote:
....
>I assume you've not done my work with or on DBs. A lot of application
>logic not to mention triggers are in the database itself in the form of
>PL/SQL, t-SQl (or whatever procedural extension of SQL the DB supports)
>procedures/functions. A lack of datetime functionality would be a show
>stopper in a lot of cases.

This is A) obvious and B) Off-topic.

Feel free to continue to discuss it - but please observe etiquette and
change the subject line accordingly (as I have done).

At this point, the likelihood that OP will ever get a meaningful/helpful
response is quickly approaching zero. Incidentally, I don't quite see what
OP's problem is - i.e., it looks like he's already come pretty close to a
solution already (by himself).

--
Atheism:
It's like being the only sober person in the car, and nobody will let you drive.

On Sat, 16 Mar 2024 10:38:50 -0000 (UTC)
gazelle@shell.xmission.com (Kenny McCormack) wrote:
>In article <ut3rse$2ri7l$1@dont-email.me>, <Muttley@dastardlyhq.com> wrote:
>....
>>I assume you've not done my work with or on DBs. A lot of application
>>logic not to mention triggers are in the database itself in the form of
>>PL/SQL, t-SQl (or whatever procedural extension of SQL the DB supports)
>>procedures/functions. A lack of datetime functionality would be a show
>>stopper in a lot of cases.
>
>This is A) obvious and B) Off-topic.

Not obvious to the poster I replied to. As for off-topic , thats a matter of
opinion and its not as if this group gets hundreds of posts a day. This is
the first time anyone has posted since Feb 22nd.

>Feel free to continue to discuss it - but please observe etiquette and
>change the subject line accordingly (as I have done).

And I've changed it back. Most DB work is done on unix and the discussion
was programming so I'm not sure what your problem is. Are you having a bad day,
get out of bed the wrong side? And just for the record - this isn't your
personal newsgroup.

>At this point, the likelihood that OP will ever get a meaningful/helpful
>response is quickly approaching zero. Incidentally, I don't quite see what

I suspect the intersect of the small number of people who read this group
(and no doubt getting ever smaller due to attitudes like yours) and those
who've done mysql C API dev is somewhat limited to say the least.

Hi, Kenny, et al.

On Sat, 16 Mar 2024 10:38:50 +0000, Kenny McCormack wrote:

> In article <ut3rse$2ri7l$1@dont-email.me>, <Muttley@dastardlyhq.com> wrote:
> ...
>>I assume you've not done my work with or on DBs. A lot of application
>>logic not to mention triggers are in the database itself in the form of
>>PL/SQL, t-SQl (or whatever procedural extension of SQL the DB supports)
>>procedures/functions. A lack of datetime functionality would be a show
>>stopper in a lot of cases.
>
> This is A) obvious and B) Off-topic.
>
> Feel free to continue to discuss it - but please observe etiquette and
> change the subject line accordingly (as I have done).

FWIW, there are some usenet posters that I do not follow. It appears that
this thread branched out from my original post when one of those people
posted an irrelevant reply. So be it.

> At this point, the likelihood that OP will ever get a meaningful/helpful
> response is quickly approaching zero. Incidentally, I don't quite see what
> OP's problem is - i.e., it looks like he's already come pretty close to a
> solution already (by himself).

I'm glad that you think that my solution is "pretty close". It covers my
current use-case fairly well, in that I can both query a table for a datetime
value and have my program logic properly evaluate and report the value, and
I can take input dates and times, evaluate them in logic, and properly insert
them into a table.

But, my "solution" doesn't cover date management corner cases[1] at all, and I
was hoping that there was a more general-purpose solution "out there"
somewhere :-) The good news is that I can continue to enhance and expand my
existing conversion logic, while the search continues.

[1] Necessary considerations that I /know/ the code doesn't cover:
a) MYSQL_TIME years extend from 1000AD TO 9999AD, but struct tm
years cover a greater range. I don't handle the cases where
my struct tm tm_year is out of range for MYSQL_TIME year.
Additionally, MYSQL_TIME reserves 0000-00-00 00:00:00 as a
special case, and I haven't decided how I should handle it.

b) MYSQL_TIME includes a flag that indicates "whether the time
is negative". What does that even mean? When MYSQL_TIME neg
is true, then what do the other values in MYSQL_TIME represent?

c) I completely ignore the requisite values for struct tm tm_wday
and struct tm tm_yday. Perhaps I shouldn't.

d) I fudge the value for struct tm tm_isdst.

e) I have no error handling or error reporting logic.

--
Lew Pitcher
"In Skills We Trust"

On Sat, 16 Mar 2024 10:22:06 +0000, Muttley wrote:

> On Fri, 15 Mar 2024 21:19:16 -0000 (UTC)
> Lawrence D'Oliveiro <ldo@nz.invalid> wrote:
>>On Fri, 15 Mar 2024 19:18:51 -0000 (UTC), Lew Pitcher wrote:
>>
>>> I'm converting some code that uses the Mysql/Mariadb C api from text
>>> queries to prepared statements, and would like my public interfaces to
>>> use struct tm (Unix "broken-down" time) instead of MYSQL_TIME.
>>
>>Never found much need for DBMS-specific date/time conversion routines.
>>Those sorts of things belong in the application logic, not in the
>>database.

And, that's where I'm trying to put them. /BUT/, the database /stores/
date/time values that the application logic must manipulate, and it is
/the interface/ between database and application that I'm concerned about.

> I assume you've not done my work with or on DBs. A lot of application logic
> not to mention triggers are in the database itself in the form of PL/SQL, t-SQl
> (or whatever procedural extension of SQL the DB supports) procedures/functions.
> A lack of datetime functionality would be a show stopper in a lot of cases.

In my case, I have date/time values that I accept from a commandline input
that I have to pass into a prepared statement query, /and/ other prepared statement
queries that return date/time values that my program logic then has to process
and report.

For my program, it is more convenient (and more conventional) if I express
date/time values as "broken-down time" in struct tm variables. However, the
MYSQL C prepared statement interface requires all DATE and TIME values be
expressed as MYSQL_TIME variables.

Rather than restrict my program logic to use MYSQL only, I prefer to use the
generic Unix functions, and isolate the MYSQL code in a separate module. The
interface to that module would accept struct tm date/time inputs and
return struct tm date/time outputs. Internally, it would transform struct tm
data to MYSQL_TIME for the "param" side of the queries, and MYSQL_TIME data to
struct tm for the "result" side of the queries.

My sample code, in the OP, serves (more or less) for the single query that
I am working on now. BUT, rather than reinvent the wheel, I was hoping to
locate a more general purpose set of functions that I can use on /all/ the
queries in my current project, and reuse for other projects, going forward.

--
Lew Pitcher
"In Skills We Trust"

On Sat, 16 Mar 2024 15:43:10 -0000 (UTC)
Lew Pitcher <lew.pitcher@digitalfreehold.ca> wrote:
>For my program, it is more convenient (and more conventional) if I express
>date/time values as "broken-down time" in struct tm variables. However, the
>MYSQL C prepared statement interface requires all DATE and TIME values be
>expressed as MYSQL_TIME variables.

All the databases I've worked on have had awkward (from a C/C++ API POV)
datetime structures. I imagine the reason is that their date range is way
broader than the unix one whether 32 or 64 bit so can't be stored as a simple
integer value.

On Sat, 16 Mar 2024 16:08:03 +0000, Muttley wrote:

> On Sat, 16 Mar 2024 15:43:10 -0000 (UTC)
> Lew Pitcher <lew.pitcher@digitalfreehold.ca> wrote:
>>For my program, it is more convenient (and more conventional) if I express
>>date/time values as "broken-down time" in struct tm variables. However, the
>>MYSQL C prepared statement interface requires all DATE and TIME values be
>>expressed as MYSQL_TIME variables.
>
> All the databases I've worked on have had awkward (from a C/C++ API POV)
> datetime structures. I imagine the reason is that their date range is way
> broader than the unix one whether 32 or 64 bit so can't be stored as a simple
> integer value.

Apparently, the MYSQL date range is alot /narrower/ than the Unix (64bit) time_t
timestamp and struct tm ones.

--
Lew Pitcher
"In Skills We Trust"

Muttley@dastardlyhq.com <Muttley@dastardlyhq.com> wrote at 16:08 this Saturday (GMT):
> On Sat, 16 Mar 2024 15:43:10 -0000 (UTC)
> Lew Pitcher <lew.pitcher@digitalfreehold.ca> wrote:
>>For my program, it is more convenient (and more conventional) if I express
>>date/time values as "broken-down time" in struct tm variables. However, the
>>MYSQL C prepared statement interface requires all DATE and TIME values be
>>expressed as MYSQL_TIME variables.
>
> All the databases I've worked on have had awkward (from a C/C++ API POV)
> datetime structures. I imagine the reason is that their date range is way
> broader than the unix one whether 32 or 64 bit so can't be stored as a simple
> integer value.

I thought the 64b time limit is sometime after the sun explodes though.
--
user <candycane> is generated from /dev/urandom

On Sat, 16 Mar 2024 15:43:10 -0000 (UTC), Lew Pitcher wrote:

> On Sat, 16 Mar 2024 10:22:06 +0000, Muttley wrote:
>
>> On Fri, 15 Mar 2024 21:19:16 -0000 (UTC)
>> Lawrence D'Oliveiro <ldo@nz.invalid> wrote:
>>
>>>Never found much need for DBMS-specific date/time conversion routines.
>>>Those sorts of things belong in the application logic, not in the
>>>database.
>
> And, that's where I'm trying to put them. /BUT/, the database /stores/
> date/time values that the application logic must manipulate, and it is
> /the interface/ between database and application that I'm concerned
> about.

If you are trying to convert your database schema to get rid of date/time
types, I would recommend using a higher-level language than C for this
purpose. After all, it’s a one-off-type task, isn’t it? A language like
Python offers better data typing that would make the task easier.

On Sat, 16 Mar 2024 16:30:57 -0000 (UTC), Lew Pitcher wrote:

> Apparently, the MYSQL date range is alot /narrower/ than the Unix
> (64bit) time_t timestamp and struct tm ones.

Another good reason for avoiding it?

Lawrence D'Oliveiro <ldo@nz.invalid> writes:
>On Sat, 16 Mar 2024 15:43:10 -0000 (UTC), Lew Pitcher wrote:
>
>> On Sat, 16 Mar 2024 10:22:06 +0000, Muttley wrote:
>>
>>> On Fri, 15 Mar 2024 21:19:16 -0000 (UTC)
>>> Lawrence D'Oliveiro <ldo@nz.invalid> wrote:
>>>
>>>>Never found much need for DBMS-specific date/time conversion routines.
>>>>Those sorts of things belong in the application logic, not in the
>>>>database.
>>
>> And, that's where I'm trying to put them. /BUT/, the database /stores/
>> date/time values that the application logic must manipulate, and it is
>> /the interface/ between database and application that I'm concerned
>> about.
>
>If you are trying to convert your database schema to get rid of date/time
>types, I would recommend using a higher-level language than C for this

Another non-responsive and useless answer.

On Fri, 15 Mar 2024 19:18:51 -0000 (UTC), Lew Pitcher
<lew.pitcher@digitalfreehold.ca> wrote:

> Hi, Guys
>
> I'm converting some code that uses the Mysql/Mariadb C api from
> text queries to prepared statements, and would like my public
> interfaces to use struct tm (Unix "broken-down" time) instead of
> MYSQL_TIME.

[....]

It might be worth to have a look at the source code for the
SQLite date/time functions, it's open source, public domain
even.

https://www.sqlite.org/lang_datefunc.html
https://www.sqlite.org/src/file?name=src/date.c&ci=trunk

I have no idea if the internal structures fit your use case in
any way.
--
Regards,
Kees Nuyt