Re: Converting a Number to a Date

From: <pschauss_at_parker.com>
Date: Wed, 14 Jun 2000 13:10:04 -0700
Message-Id: <10528.109296@fatcity.com>

David,

We use the "seconds since 1970" time format for date/time manipulations inside our C programs, converting it to and from Oracle format when we store it in the tables.

Here is what we use (see additional notes below):

oracledate.h:

---

/* Oracle internal date format */
/* Because PROC does not expand header files, .pc files must define */
/* this structure as char [7] in any host variables. */

typedef struct

    {     unsigned char  century;
     unsigned char  year;
     unsigned char  mon;
     unsigned char  day;
     unsigned char  hour;
     unsigned char  min;
     unsigned char  sec;

    } oracleDate_t;

---

oracledate.c

/************************************************************************/

#include <time.h>
#include <oracledate.h>

/* timezone and altzone are global variables provided by the unix */
/* system to allow user code to convert between UTC (gmt) and */
/* local time. */

extern    time_t    timezone, /*  difference from UTC             */
          altzone;  /*  difference from UTC if DST is   */
                    /*  in effect.                 */

/************************************************************************/


/*  dateU2oracle                                   */

/*                                            */

/*  Converts a Unix date in time_t format to oracle format        */

/************************************************************************/

void dateU2oracle ( odate, udate )
oracleDate_t   *odate;             /*  Oracle format date (returned)   */
time_t    udate;              /*  Unix date format                */

{
struct tm *time;

    time = gmtime ( &udate );

    odate->century = 119 + time->tm_year/100;
    odate->year    = 100 + time->tm_year % 100;
    odate->mon     = 1 + time->tm_mon;
    odate->day     = time->tm_mday;
    odate->hour    = 1 + time->tm_hour;
    odate->min     = 1 + time->tm_min;
    odate->sec     = 1 + time->tm_sec;

}

/************************************************************************/


/*  dateOracle2U                                   */

/*                                            */

/*  Converts oracle date to Unix time_t format                    */

/************************************************************************/

void dateOracle2U ( udate, odate )
time_t *udate;
oracleDate_t *odate;
{
int year;
struct tm time;

    year = (odate->century - 100 ) * 100 + (odate->year - 100 );

    time.tm_year = year -1900;
    time.tm_mon  = odate->mon - 1;
    time.tm_mday = odate->day;
    time.tm_hour = odate->hour - 1;
    time.tm_min  = odate->min - 1;
    time.tm_sec  = odate->sec - 1;

    time.tm_wday = 0;
    time.tm_yday = 0;
    time.tm_isdst = -1;       /*  Dst not known              */

    *udate = mktime ( &time );

/* Convert to UTC */

    if ( time.tm_isdst == 0 )
     *udate -= timezone;
    else
     *udate -= altzone;

}

---

This code was written six years ago (Oracle 7.0.x ) and has survived Y2K and Oracle 8.0.5 (I haven't tried it in 8.1.x yet).

Nevertheless, if I were writing it today, I would pass Oracle the date in string form ( MM-DD-YYYY HH24:MI:SS ) as it takes a bit of magic in PRO*C to get Oracle to send and receive dates in its internal 7 byte format.

Peter Schauss
Parker Hannifin Corp.
Smithtown, NY

David Barbour <DBarbour_at_connectsouth.com>@fatcity.com on 06/14/2000 04:20:22 PM

Please respond to ORACLE-L_at_fatcity.com

Sent by: root_at_fatcity.com

To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com> cc:
Subject: Converting a Number to a Date

I have what appears to be a UNIX-style timestamp series of numbers which I have to convert back to dates.

They're in the format 960770731

I've tried a number of things, but haven't figured it out yet. Has anybody encountered something similar?

David A. Barbour
Oracle DBA - ConnectSouth
512-681-9438
dbarbour_at_connectsouth.com

--
Author: David Barbour
  INET: DBarbour_at_connectsouth.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L

Received on Wed Jun 14 2000 - 15:10:04 CDT