dt_test.pgc

PostgreSQL 8.1.4的源码 适用于Linux下的开源数据库系统

#include <stdio.h>
#include <string.h>
#include <stdlib.h>
#include <pgtypes_date.h>
#include <pgtypes_timestamp.h>
#include <pgtypes_interval.h>

int
main(void)
{
	exec sql begin declare section;
		date date1;
		timestamp ts1;
		interval iv1;
		char *text;
	exec sql end declare section;
	date date2;
	int mdy[3] = { 4, 19, 1998 };
	char *fmt, *out, *in;
	char *d1 = "Mon Jan 17 1966";
	char *t1 = "2000-7-12 17:34:29";
	int i;
	FILE *dbgs;

        if ((dbgs = fopen("log", "w")) != NULL)
                 ECPGdebug(1, dbgs);
        exec sql whenever sqlerror do sqlprint();
        exec sql connect to mm;
        exec sql create table date_test (d date, ts timestamp, iv interval);
	exec sql set datestyle to iso;

	date1 = PGTYPESdate_from_asc(d1, NULL); 
	ts1 = PGTYPEStimestamp_from_asc(t1, NULL); 
	
	exec sql insert into date_test(d, ts, iv) values (:date1, :ts1, now()-'Mon Jan 17 1966');

	exec sql select * into :date1, :ts1 , :iv1 from date_test where d=:date1;
	
	text = PGTYPESdate_to_asc(date1);
	printf ("Date: %s\n", text);
	
	text = PGTYPEStimestamp_to_asc(ts1);
	printf ("timestamp: %s\n", text);
	
	text = PGTYPESinterval_to_asc(&iv1);
	printf ("interval: %s\n", text);

	PGTYPESdate_mdyjul(mdy, &date2);
	printf("m: %d, d: %d, y: %d\n", mdy[0], mdy[1], mdy[2]);
	/* reset */
	mdy[0] = mdy[1] = mdy[2] = 0;

	printf("date seems to get encoded to julian %ld\n", date2);
	
	PGTYPESdate_julmdy(date2, mdy);
	printf("m: %d, d: %d, y: %d\n", mdy[0], mdy[1], mdy[2]);
       
	ts1 = PGTYPEStimestamp_from_asc("2003-12-04 17:34:29", NULL);
	text = PGTYPEStimestamp_to_asc(ts1);

	printf("date_day of %s is %d\n", text, PGTYPESdate_dayofweek(ts1));

	PGTYPESdate_today(&date1);
	text = PGTYPESdate_to_asc(date1);
	printf("today is %s\n", text);

	fmt = "(ddd), mmm. dd, yyyy, repeat: (ddd), mmm. dd, yyyy. end";
	out = (char*) malloc(strlen(fmt) + 1);

	PGTYPESdate_fmt_asc(date1, fmt, out);
	printf("Today in format \"%s\" is \"%s\"\n", fmt, out);
	free(out);

	/* rdate_defmt_asc() */

	date1 = 0; text = "";
	fmt = "yy/mm/dd";
	in = "In the year 1995, the month of December, it is the 25th day";
	/*    0123456789012345678901234567890123456789012345678901234567890
	 *    0         1         2         3         4         5         6
	 */
	PGTYPESdate_defmt_asc(&date1, fmt, in);
	text = PGTYPESdate_to_asc(date1);
	printf("date_defmt_asc1: %s\n", text);

	date1 = 0; text = "";
	fmt = "mmmm. dd. yyyy";
	in = "12/25/95";
	PGTYPESdate_defmt_asc(&date1, fmt, in);
	text = PGTYPESdate_to_asc(date1);
	printf("date_defmt_asc2: %s\n", text);

	date1 = 0; text = "";
	fmt = "yy/mm/dd";
	in = "95/12/25";
	PGTYPESdate_defmt_asc(&date1, fmt, in);
	text = PGTYPESdate_to_asc(date1);
	printf("date_defmt_asc3: %s\n", text);

	date1 = 0; text = "";
	fmt = "yy/mm/dd";
	in = "1995, December 25th";
	PGTYPESdate_defmt_asc(&date1, fmt, in);
	text = PGTYPESdate_to_asc(date1);
	printf("date_defmt_asc4: %s\n", text);

	date1 = 0; text = "";
	fmt = "dd-mm-yy";
	in = "This is 25th day of December, 1995";
	PGTYPESdate_defmt_asc(&date1, fmt, in);
	text = PGTYPESdate_to_asc(date1);
	printf("date_defmt_asc5: %s\n", text);

	date1 = 0; text = "";
	fmt = "mmddyy";
	in = "Dec. 25th, 1995";
	PGTYPESdate_defmt_asc(&date1, fmt, in);
	text = PGTYPESdate_to_asc(date1);
	printf("date_defmt_asc6: %s\n", text);

	date1 = 0; text = "";
	fmt = "mmm. dd. yyyy";
	in = "dec 25th 1995";
	PGTYPESdate_defmt_asc(&date1, fmt, in);
	text = PGTYPESdate_to_asc(date1);
	printf("date_defmt_asc7: %s\n", text);

	date1 = 0; text = "";
	fmt = "mmm. dd. yyyy";
	in = "DEC-25-1995";
	PGTYPESdate_defmt_asc(&date1, fmt, in);
	text = PGTYPESdate_to_asc(date1);
	printf("date_defmt_asc8: %s\n", text);

	date1 = 0; text = "";
	fmt = "mm yy   dd.";
	in = "12199525";
	PGTYPESdate_defmt_asc(&date1, fmt, in);
	text = PGTYPESdate_to_asc(date1);
	printf("date_defmt_asc9: %s\n", text);

	date1 = 0; text = "";
	fmt = "yyyy fierj mm   dd.";
	in = "19951225";
	PGTYPESdate_defmt_asc(&date1, fmt, in);
	text = PGTYPESdate_to_asc(date1);
	printf("date_defmt_asc10: %s\n", text);

	date1 = 0; text = "";
	fmt = "mm/dd/yy";
	in = "122595";
	PGTYPESdate_defmt_asc(&date1, fmt, in);
	text = PGTYPESdate_to_asc(date1);
	printf("date_defmt_asc12: %s\n", text);

	PGTYPEStimestamp_current(&ts1);
	text = PGTYPEStimestamp_to_asc(ts1);
	printf("timestamp_current: Now: %s\n", text);

	ts1 = PGTYPEStimestamp_from_asc("96-02-29", NULL);
	text = PGTYPEStimestamp_to_asc(ts1);
	printf("timestamp_to_asc1: %s\n", text);

	ts1 = PGTYPEStimestamp_from_asc("1994-02-11 3:10:35", NULL);
	text = PGTYPEStimestamp_to_asc(ts1);
	printf("timestamp_to_asc2: %s\n", text);

	ts1 = PGTYPEStimestamp_from_asc("1994-02-11 26:10:35", NULL);
	text = PGTYPEStimestamp_to_asc(ts1);
	printf("timestamp_to_asc3: %s\n", text);

/*	abc-03:10:35-def-02/11/94-gh  */
/*      12345678901234567890123456789 */

	out = (char*) malloc(32);
	i = PGTYPEStimestamp_fmt_asc(&ts1, out, 31, "abc-%X-def-%x-ghi%%");
	printf("timestamp_fmt_asc: %d: %s\n", i, out);

	fmt = "This is a %m/%d/%y %H-%Ml%Stest";
	in =  "This is a 4/12/80 3-39l12test";
	i = PGTYPEStimestamp_defmt_asc(in, fmt, &ts1);
	text = PGTYPEStimestamp_to_asc(ts1);
	printf("timestamp_defmt_asc(%s, %s) = %s, error: %d\n", in, fmt, text, i);

	fmt = "%a %b %d %H:%M:%S %z %Y";
	in =  "Tue Jul 22 17:28:44 +0200 2003";
	i = PGTYPEStimestamp_defmt_asc(in, fmt, &ts1);
	text = PGTYPEStimestamp_to_asc(ts1);
	printf("timestamp_defmt_asc(%s, %s) = %s, error: %d\n", in, fmt, text, i);

	fmt = "%a %b %d %H:%M:%S %z %Y";
	in =  "Tue Feb 29 17:28:44 +0200 2000";
	i = PGTYPEStimestamp_defmt_asc(in, fmt, &ts1);
	text = PGTYPEStimestamp_to_asc(ts1);
	printf("timestamp_defmt_asc(%s, %s) = %s, error: %d\n", in, fmt, text, i);

	fmt = "%a %b %d %H:%M:%S %z %Y";
	in =  "Tue Feb 29 17:28:44 +0200 1900";
	i = PGTYPEStimestamp_defmt_asc(in, fmt, &ts1);
	text = PGTYPEStimestamp_to_asc(ts1);
	printf("timestamp_defmt_asc(%s, %s) = %s, error (should be error!): %d\n", in, fmt, text, i);

	fmt = "%a %b %d %H:%M:%S %z %Y";
	in =  "Tue Feb 29 17:28:44 +0200 1996";
	i = PGTYPEStimestamp_defmt_asc(in, fmt, &ts1);
	text = PGTYPEStimestamp_to_asc(ts1);
	printf("timestamp_defmt_asc(%s, %s) = %s, error: %d\n", in, fmt, text, i);

	fmt = "%b %d %H:%M:%S %z %Y";
	in =  "      Jul 31 17:28:44 +0200 1996";
	i = PGTYPEStimestamp_defmt_asc(in, fmt, &ts1);
	text = PGTYPEStimestamp_to_asc(ts1);
	printf("timestamp_defmt_asc(%s, %s) = %s, error: %d\n", in, fmt, text, i);

	fmt = "%b %d %H:%M:%S %z %Y";
	in =  "      Jul 32 17:28:44 +0200 1996";
	i = PGTYPEStimestamp_defmt_asc(in, fmt, &ts1);
	text = PGTYPEStimestamp_to_asc(ts1);
	printf("timestamp_defmt_asc(%s, %s) = %s, error (should be error!): %d\n", in, fmt, text, i);

	fmt = "%a %b %d %H:%M:%S %z %Y";
	in =  "Tue Feb 29 17:28:44 +0200 1997";
	i = PGTYPEStimestamp_defmt_asc(in, fmt, &ts1);
	text = PGTYPEStimestamp_to_asc(ts1);
	printf("timestamp_defmt_asc(%s, %s) = %s, error (should be error!): %d\n", in, fmt, text, i);

	fmt = "%";
	in =  "Tue Jul 22 17:28:44 +0200 2003";
	i = PGTYPEStimestamp_defmt_asc(in, fmt, &ts1);
	text = PGTYPEStimestamp_to_asc(ts1);
	printf("timestamp_defmt_asc(%s, %s) = %s, error (should be error!): %d\n", in, fmt, text, i);

	fmt = "a %";
	in =  "Tue Jul 22 17:28:44 +0200 2003";
	i = PGTYPEStimestamp_defmt_asc(in, fmt, &ts1);
	text = PGTYPEStimestamp_to_asc(ts1);
	printf("timestamp_defmt_asc(%s, %s) = %s, error (should be error!): %d\n", in, fmt, text, i);

	fmt = "%b, %d %H_%M`%S %z %Y";
	in =  "    Jul, 22 17_28 `44 +0200  2003  ";
	i = PGTYPEStimestamp_defmt_asc(in, fmt, &ts1);
	text = PGTYPEStimestamp_to_asc(ts1);
	printf("timestamp_defmt_asc(%s, %s) = %s, error: %d\n", in, fmt, text, i);

	fmt = "%a %b %%%d %H:%M:%S %Z %Y";
	in =  "Tue Jul %22 17:28:44 CEST 2003";
	i = PGTYPEStimestamp_defmt_asc(in, fmt, &ts1);
	text = PGTYPEStimestamp_to_asc(ts1);
	printf("timestamp_defmt_asc(%s, %s) = %s, error: %d\n", in, fmt, text, i);

	fmt = "%a %b %%%d %H:%M:%S %Z %Y";
	in =  "Tue Jul %22 17:28:44 CEST 2003";
	i = PGTYPEStimestamp_defmt_asc(in, fmt, &ts1);
	text = PGTYPEStimestamp_to_asc(ts1);
	printf("timestamp_defmt_asc(%s, %s) = %s, error: %d\n", in, fmt, text, i);

	fmt = "abc%n %C %B %%%d %H:%M:%S %Z %Y";
	in =  "abc\n   19 October %22 17:28:44 CEST 2003";
	i = PGTYPEStimestamp_defmt_asc(in, fmt, &ts1);
	text = PGTYPEStimestamp_to_asc(ts1);
	printf("timestamp_defmt_asc(%s, %s) = %s, error: %d\n", in, fmt, text, i);

	fmt = "abc%n %C %B %%%d %H:%M:%S %Z %y";
	in =  "abc\n   18 October %34 17:28:44 CEST 80";
	i = PGTYPEStimestamp_defmt_asc(in, fmt, &ts1);
	text = PGTYPEStimestamp_to_asc(ts1);
	printf("timestamp_defmt_asc(%s, %s) = %s, error (should be error!): %d\n", in, fmt, text, i);

	fmt = "";
	in =  "abc\n   18 October %34 17:28:44 CEST 80";
	i = PGTYPEStimestamp_defmt_asc(in, fmt, &ts1);
	text = PGTYPEStimestamp_to_asc(ts1);
	printf("timestamp_defmt_asc(%s, %s) = %s, error (should be error!): %d\n", in, fmt, text, i);

	fmt = NULL;
	in =  "1980-04-12 3:49:44      ";
	i = PGTYPEStimestamp_defmt_asc(in, fmt, &ts1);
	text = PGTYPEStimestamp_to_asc(ts1);
	printf("timestamp_defmt_asc(%s, %s) = %s, error: %d\n", in, fmt, text, i);

	fmt = "%B %d, %Y. Time: %I:%M%p";
	in =  "July 14, 1988. Time: 9:15am";
	i = PGTYPEStimestamp_defmt_asc(in, fmt, &ts1);
	text = PGTYPEStimestamp_to_asc(ts1);
	printf("timestamp_defmt_asc(%s, %s) = %s, error: %d\n", in, fmt, text, i);

	in = "September 6 at 01:30 pm in the year 1983";
	fmt = "%B %d at %I:%M %p in the year %Y";
	i = PGTYPEStimestamp_defmt_asc(in, fmt, &ts1);
	text = PGTYPEStimestamp_to_asc(ts1);
	printf("timestamp_defmt_asc(%s, %s) = %s, error: %d\n", in, fmt, text, i);

	in = "  1976, July 14. Time: 9:15am";
	fmt = "%Y,   %B %d. Time: %I:%M %p";
	i = PGTYPEStimestamp_defmt_asc(in, fmt, &ts1);
	text = PGTYPEStimestamp_to_asc(ts1);
	printf("timestamp_defmt_asc(%s, %s) = %s, error: %d\n", in, fmt, text, i);

	in = "  1976, July 14. Time: 9:15 am";
	fmt = "%Y,   %B %d. Time: %I:%M%p";
	i = PGTYPEStimestamp_defmt_asc(in, fmt, &ts1);
	text = PGTYPEStimestamp_to_asc(ts1);
	printf("timestamp_defmt_asc(%s, %s) = %s, error: %d\n", in, fmt, text, i);

	in = "  1976, P.M. July 14. Time: 9:15";
	fmt = "%Y, %P  %B %d. Time: %I:%M";
	i = PGTYPEStimestamp_defmt_asc(in, fmt, &ts1);
	text = PGTYPEStimestamp_to_asc(ts1);
	printf("timestamp_defmt_asc(%s, %s) = %s, error: %d\n", in, fmt, text, i);
	exec sql rollback;
        exec sql disconnect;

	if (dbgs != NULL)
		fclose(dbgs);
						
	return (0);
}