Looking for a MYSQL_TIME to struct tm conversion routine

[Lew Pitcher]

[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

[Lawrence D'Oliveiro]

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.

[Kenny McCormack]

Post by Lawrence D'Oliveiro
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.

[Kaz Kylheku]

Post by Kenny McCormack

Post by Lawrence D'Oliveiro
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.

[M***@dastardlyhq.com]

On Fri, 15 Mar 2024 21:19:16 -0000 (UTC)

Post by Lawrence D'Oliveiro

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.

[Kenny McCormack]

In article <ut3rse$2ri7l$***@dont-email.me>, <***@dastardlyhq.com> wrote:
...

Post by M***@dastardlyhq.com
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).

[M***@dastardlyhq.com]

On Sat, 16 Mar 2024 10:38:50 -0000 (UTC)

Post by Kenny McCormack
....

Post by M***@dastardlyhq.com
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.

Post by Kenny McCormack
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.

Post by Kenny McCormack
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.

[Lew Pitcher]

Hi, Kenny, et al.

Post by Kenny McCormack
...

Post by M***@dastardlyhq.com
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.

Post by Kenny McCormack
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]

Post by M***@dastardlyhq.com
On Fri, 15 Mar 2024 21:19:16 -0000 (UTC)

Post by Lawrence D'Oliveiro

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.

Post by M***@dastardlyhq.com
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.

[M***@dastardlyhq.com]

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

Post by Lew Pitcher
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.

[Lew Pitcher]

Post by M***@dastardlyhq.com
On Sat, 16 Mar 2024 15:43:10 -0000 (UTC)

Post by Lew Pitcher
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.

[Lawrence D'Oliveiro]

Post by Lew Pitcher
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?

[candycanearter07]

Post by M***@dastardlyhq.com
On Sat, 16 Mar 2024 15:43:10 -0000 (UTC)

Post by Lew Pitcher
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.

[Lawrence D'Oliveiro]

Post by Lew Pitcher

Post by M***@dastardlyhq.com
On Fri, 15 Mar 2024 21:19:16 -0000 (UTC)

Post by Lawrence D'Oliveiro
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.

[Scott Lurndal]

Post by Lawrence D'Oliveiro

Post by Lew Pitcher

Post by M***@dastardlyhq.com
On Fri, 15 Mar 2024 21:19:16 -0000 (UTC)

Post by Lawrence D'Oliveiro
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.

[Kees Nuyt]

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

Post by Lew Pitcher
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.