[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"

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

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.

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

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.

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

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

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

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.

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

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.

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

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.

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

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"

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

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.

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

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"

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

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

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

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"

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

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.

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

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?

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

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.

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

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

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)