datetime.c 14.2 KB
Newer Older
1 2 3
/*-------------------------------------------------------------------------
 *
 * datetime.c--
4
 *	  implements DATE and TIME data types specified in SQL-92 standard
5 6 7 8 9
 *
 * Copyright (c) 1994-5, Regents of the University of California
 *
 *
 * IDENTIFICATION
10
 *	  $Header: /cvsroot/pgsql/src/backend/utils/adt/datetime.c,v 1.26 1998/12/31 16:30:56 thomas Exp $
11 12 13
 *
 *-------------------------------------------------------------------------
 */
14
#include <stdio.h>				/* for sprintf() */
15
#include <string.h>
16
#include <limits.h>
17

18
#include "postgres.h"
Marc G. Fournier's avatar
Marc G. Fournier committed
19 20 21
#ifdef HAVE_FLOAT_H
#include <float.h>
#endif
22 23 24 25 26
#include "miscadmin.h"
#include "utils/builtins.h"
#include "utils/nabstime.h"
#include "utils/datetime.h"
#include "access/xact.h"
27

28
static int	date2tm(DateADT dateVal, int *tzp, struct tm * tm, double *fsec, char **tzn);
29 30


31
static int	day_tab[2][12] = {
32 33
	{31, 28, 31, 30, 31, 30, 31, 31, 30, 31, 30, 31},
{31, 29, 31, 30, 31, 30, 31, 31, 30, 31, 30, 31}};
34 35 36

#define isleap(y) (((y % 4) == 0 && (y % 100) != 0) || (y % 400) == 0)

37 38 39 40 41 42 43 44 45 46 47 48 49
#define UTIME_MINYEAR (1901)
#define UTIME_MINMONTH (12)
#define UTIME_MINDAY (14)
#define UTIME_MAXYEAR (2038)
#define UTIME_MAXMONTH (01)
#define UTIME_MAXDAY (18)

#define IS_VALID_UTIME(y,m,d) (((y > UTIME_MINYEAR) \
 || ((y == UTIME_MINYEAR) && ((m > UTIME_MINMONTH) \
  || ((m == UTIME_MINMONTH) && (d >= UTIME_MINDAY))))) \
 && ((y < UTIME_MAXYEAR) \
 || ((y == UTIME_MAXYEAR) && ((m < UTIME_MAXMONTH) \
  || ((m == UTIME_MAXMONTH) && (d <= UTIME_MAXDAY))))))
50 51

/*****************************************************************************
52
 *	 Date ADT
53 54 55 56 57 58 59 60 61
 *****************************************************************************/


/* date_in()
 * Given date text string, convert to internal date format.
 */
DateADT
date_in(char *str)
{
62 63 64 65 66 67 68 69 70 71
	DateADT		date;
	double		fsec;
	struct tm	tt,
			   *tm = &tt;
	int			tzp;
	int			dtype;
	int			nf;
	char	   *field[MAXDATEFIELDS];
	int			ftype[MAXDATEFIELDS];
	char		lowstr[MAXDATELEN + 1];
72 73

	if (!PointerIsValid(str))
74
		elog(ERROR, "Bad (null) date external representation", NULL);
75 76

#ifdef DATEDEBUG
77
	printf("date_in- input string is %s\n", str);
78
#endif
79 80
	if ((ParseDateTime(str, lowstr, field, ftype, MAXDATEFIELDS, &nf) != 0)
	 || (DecodeDateTime(field, ftype, nf, &dtype, tm, &fsec, &tzp) != 0))
81
		elog(ERROR, "Bad date external representation %s", str);
82 83 84

	switch (dtype)
	{
85 86
		case DTK_DATE:
			break;
87

88 89 90
		case DTK_CURRENT:
			GetCurrentTime(tm);
			break;
91

92 93 94 95 96
		case DTK_EPOCH:
			tm->tm_year = 1970;
			tm->tm_mon = 1;
			tm->tm_mday = 1;
			break;
97

98
		default:
99
			elog(ERROR, "Unrecognized date external representation %s", str);
100
	}
101

102
	if (tm->tm_year < 0 || tm->tm_year > 32767)
103
		elog(ERROR, "date_in: year must be limited to values 0 through 32767 in '%s'", str);
104
	if (tm->tm_mon < 1 || tm->tm_mon > 12)
105
		elog(ERROR, "date_in: month must be limited to values 1 through 12 in '%s'", str);
106
	if (tm->tm_mday < 1 || tm->tm_mday > day_tab[isleap(tm->tm_year)][tm->tm_mon - 1])
107
		elog(ERROR, "date_in: day must be limited to values 1 through %d in '%s'",
108
			 day_tab[isleap(tm->tm_year)][tm->tm_mon - 1], str);
109

110
	date = (date2j(tm->tm_year, tm->tm_mon, tm->tm_mday) - date2j(2000, 1, 1));
111

112
	return date;
113
}	/* date_in() */
114 115 116 117

/* date_out()
 * Given internal format date, convert to text string.
 */
118
char *
119 120
date_out(DateADT date)
{
121 122 123 124
	char	   *result;
	struct tm	tt,
			   *tm = &tt;
	char		buf[MAXDATELEN + 1];
125

126 127
	j2date((date + date2j(2000, 1, 1)),
		   &(tm->tm_year), &(tm->tm_mon), &(tm->tm_mday));
128

129
	EncodeDateOnly(tm, DateStyle, buf);
130

131
	result = palloc(strlen(buf) + 1);
132

133
	strcpy(result, buf);
134

135
	return result;
136
}	/* date_out() */
137 138 139 140

bool
date_eq(DateADT dateVal1, DateADT dateVal2)
{
141
	return dateVal1 == dateVal2;
142 143 144 145 146
}

bool
date_ne(DateADT dateVal1, DateADT dateVal2)
{
147
	return dateVal1 != dateVal2;
148 149 150 151 152
}

bool
date_lt(DateADT dateVal1, DateADT dateVal2)
{
153
	return dateVal1 < dateVal2;
154
}	/* date_lt() */
155 156 157 158

bool
date_le(DateADT dateVal1, DateADT dateVal2)
{
159
	return dateVal1 <= dateVal2;
160
}	/* date_le() */
161 162 163 164

bool
date_gt(DateADT dateVal1, DateADT dateVal2)
{
165
	return dateVal1 > dateVal2;
166
}	/* date_gt() */
167 168 169 170

bool
date_ge(DateADT dateVal1, DateADT dateVal2)
{
171
	return dateVal1 >= dateVal2;
172
}	/* date_ge() */
173 174 175 176

int
date_cmp(DateADT dateVal1, DateADT dateVal2)
{
177 178 179 180 181
	if (dateVal1 < dateVal2)
		return -1;
	else if (dateVal1 > dateVal2)
		return 1;
	return 0;
182
}	/* date_cmp() */
183 184 185 186

DateADT
date_larger(DateADT dateVal1, DateADT dateVal2)
{
187
	return date_gt(dateVal1, dateVal2) ? dateVal1 : dateVal2;
188
}	/* date_larger() */
189 190 191 192

DateADT
date_smaller(DateADT dateVal1, DateADT dateVal2)
{
193
	return date_lt(dateVal1, dateVal2) ? dateVal1 : dateVal2;
194
}	/* date_smaller() */
195

196 197
/* Compute difference between two dates in days.
 */
198 199 200
int4
date_mi(DateADT dateVal1, DateADT dateVal2)
{
201
	return dateVal1 - dateVal2;
202
}	/* date_mi() */
203 204

/* Add a number of days to a date, giving a new date.
205 206
 * Must handle both positive and negative numbers of days.
 */
207 208 209
DateADT
date_pli(DateADT dateVal, int4 days)
{
210
	return dateVal + days;
211
}	/* date_pli() */
212

213 214
/* Subtract a number of days from a date, giving a new date.
 */
215 216 217
DateADT
date_mii(DateADT dateVal, int4 days)
{
218
	return date_pli(dateVal, -days);
219
}	/* date_mii() */
220

221 222 223 224

/* date_datetime()
 * Convert date to datetime data type.
 */
225
DateTime   *
226
date_datetime(DateADT dateVal)
227
{
228 229 230 231 232 233
	DateTime   *result;
	struct tm	tt,
			   *tm = &tt;
	int			tz;
	double		fsec = 0;
	char	   *tzn;
234

235
	result = palloc(sizeof(DateTime));
236

237
	if (date2tm(dateVal, &tz, tm, &fsec, &tzn) != 0)
238
		elog(ERROR, "Unable to convert date to datetime", NULL);
239 240

#ifdef DATEDEBUG
241 242
	printf("date_datetime- date is %d.%02d.%02d\n", tm->tm_year, tm->tm_mon, tm->tm_mday);
	printf("date_datetime- time is %02d:%02d:%02d %.7f\n", tm->tm_hour, tm->tm_min, tm->tm_sec, fsec);
243 244
#endif

245
	if (tm2datetime(tm, fsec, &tz, result) != 0)
246
		elog(ERROR, "Datetime out of range", NULL);
247

248
	return result;
249
}	/* date_datetime() */
250 251 252 253 254 255


/* datetime_date()
 * Convert datetime to date data type.
 */
DateADT
256
datetime_date(DateTime *datetime)
257
{
258 259 260 261 262 263
	DateADT		result;
	struct tm	tt,
			   *tm = &tt;
	int			tz;
	double		fsec;
	char	   *tzn;
264

265
	if (!PointerIsValid(datetime))
266
		elog(ERROR, "Unable to convert null datetime to date", NULL);
267

268
	if (DATETIME_NOT_FINITE(*datetime))
269
		elog(ERROR, "Unable to convert datetime to date", NULL);
270

271 272 273
	if (DATETIME_IS_EPOCH(*datetime))
	{
		datetime2tm(SetDateTime(*datetime), NULL, tm, &fsec, NULL);
274

275 276 277 278
	}
	else if (DATETIME_IS_CURRENT(*datetime))
	{
		datetime2tm(SetDateTime(*datetime), &tz, tm, &fsec, &tzn);
279

280 281 282 283
	}
	else
	{
		if (datetime2tm(*datetime, &tz, tm, &fsec, &tzn) != 0)
284
			elog(ERROR, "Unable to convert datetime to date", NULL);
285
	}
286

287
	result = (date2j(tm->tm_year, tm->tm_mon, tm->tm_mday) - date2j(2000, 1, 1));
288

289
	return result;
290
}	/* datetime_date() */
291 292 293 294 295 296 297 298


/* abstime_date()
 * Convert abstime to date data type.
 */
DateADT
abstime_date(AbsoluteTime abstime)
{
299 300 301 302
	DateADT		result;
	struct tm	tt,
			   *tm = &tt;
	int			tz;
303 304 305

	switch (abstime)
	{
306 307 308
		case INVALID_ABSTIME:
		case NOSTART_ABSTIME:
		case NOEND_ABSTIME:
309
			elog(ERROR, "Unable to convert reserved abstime value to date", NULL);
310 311 312 313 314 315 316 317 318 319 320 321 322 323 324 325 326 327 328

			/*
			 * pretend to drop through to make compiler think that result
			 * will be set
			 */

		case EPOCH_ABSTIME:
			result = date2j(1970, 1, 1) - date2j(2000, 1, 1);
			break;

		case CURRENT_ABSTIME:
			GetCurrentTime(tm);
			result = date2j(tm->tm_year, tm->tm_mon, tm->tm_mday) - date2j(2000, 1, 1);
			break;

		default:
			abstime2tm(abstime, &tz, tm, NULL);
			result = date2j(tm->tm_year, tm->tm_mon, tm->tm_mday) - date2j(2000, 1, 1);
			break;
329
	}
330

331
	return result;
332
}	/* abstime_date() */
333

334 335 336 337

/* date2tm()
 * Convert date to time structure.
 * Note that date is an implicit local time, but the system calls assume
338 339
 *	that everything is GMT. So, convert to GMT, rotate to local time,
 *	and then convert again to try to get the time zones correct.
340
 */
341
static int
342
date2tm(DateADT dateVal, int *tzp, struct tm *tm, double *fsec, char **tzn)
343
{
344 345
	struct tm  *tx;
	time_t		utime;
346

347
	*fsec = 0;
348

349 350 351 352 353 354 355 356
	j2date((dateVal + date2j(2000, 1, 1)), &(tm->tm_year), &(tm->tm_mon), &(tm->tm_mday));
	tm->tm_hour = 0;
	tm->tm_min = 0;
	tm->tm_sec = 0;
	tm->tm_isdst = -1;

	if (IS_VALID_UTIME(tm->tm_year, tm->tm_mon, tm->tm_mday))
	{
357

358 359
		/* convert to system time */
		utime = ((dateVal + (date2j(2000, 1, 1) - date2j(1970, 1, 1))) * 86400);
360 361
		/* rotate to noon to get the right day in time zone */
		utime += (12 * 60 * 60);
362 363

#ifdef USE_POSIX_TIME
364
		tx = localtime(&utime);
365 366

#ifdef DATEDEBUG
367 368 369 370 371
#if defined(HAVE_TM_ZONE)
		printf("date2tm- (localtime) %d.%02d.%02d %02d:%02d:%02.0f %s dst=%d\n",
			   tx->tm_year, tx->tm_mon, tx->tm_mday, tx->tm_hour, tx->tm_min, (double) tm->tm_sec,
			   tx->tm_zone, tx->tm_isdst);
#elif defined(HAVE_INT_TIMEZONE)
372 373 374
		printf("date2tm- (localtime) %d.%02d.%02d %02d:%02d:%02.0f %s %s dst=%d\n",
			   tx->tm_year, tx->tm_mon, tx->tm_mday, tx->tm_hour, tx->tm_min, (double) tm->tm_sec,
			   tzname[0], tzname[1], tx->tm_isdst);
375
#endif
376
#endif
377 378 379 380
		tm->tm_year = tx->tm_year + 1900;
		tm->tm_mon = tx->tm_mon + 1;
		tm->tm_mday = tx->tm_mday;
		tm->tm_isdst = tx->tm_isdst;
381

382
#if defined(HAVE_TM_ZONE)
383 384 385
		tm->tm_gmtoff = tx->tm_gmtoff;
		tm->tm_zone = tx->tm_zone;

386 387 388 389 390 391
		/* tm_gmtoff is Sun/DEC-ism */
		*tzp = -(tm->tm_gmtoff);
		if (tzn != NULL)
			*tzn = (char *)tm->tm_zone;
#elif defined(HAVE_INT_TIMEZONE)
		*tzp = (tm->tm_isdst ? (timezone - 3600) : timezone);
392
		if (tzn != NULL)
393 394 395
			*tzn = tzname[(tm->tm_isdst > 0)];
#else
#error USE_POSIX_TIME is defined but neither HAVE_TM_ZONE or HAVE_INT_TIMEZONE are defined
396
#endif
397 398 399 400
#else							/* !USE_POSIX_TIME */
		*tzp = CTimeZone;		/* V7 conventions; don't know timezone? */
		if (tzn != NULL)
			*tzn = CTZName;
401 402
#endif

403 404 405 406
		/* otherwise, outside of timezone range so convert to GMT... */
	}
	else
	{
407
#ifdef DATEDEBUG
408 409
		printf("date2tm- convert %d-%d-%d %d:%d%d to datetime\n",
			   tm->tm_year, tm->tm_mon, tm->tm_mday, tm->tm_hour, tm->tm_min, tm->tm_sec);
410 411
#endif

412 413 414 415 416
		*tzp = 0;
		tm->tm_isdst = 0;
		if (tzn != NULL)
			*tzn = NULL;
	}
417

418 419 420 421 422 423 424 425 426 427 428 429
#ifdef DATEDEBUG
#if defined(HAVE_TM_ZONE)
		printf("date2tm- %d.%02d.%02d %02d:%02d:%02.0f (%d %s) dst=%d\n",
			   tm->tm_year, tm->tm_mon, tm->tm_mday, tm->tm_hour, tm->tm_min, (double) tm->tm_sec,
			   *tzp, tm->tm_zone, tm->tm_isdst);
#elif defined(HAVE_INT_TIMEZONE)
		printf("date2tm- %d.%02d.%02d %02d:%02d:%02.0f (%d %s %s) dst=%d\n",
			   tm->tm_year, tm->tm_mon, tm->tm_mday, tm->tm_hour, tm->tm_min, (double) tm->tm_sec,
			   *tzp, tzname[0], tzname[1], tm->tm_isdst);
#endif
#endif

430
	return 0;
431
}	/* date2tm() */
432

433

434
/*****************************************************************************
435
 *	 Time ADT
436 437 438
 *****************************************************************************/


439
TimeADT    *
440 441
time_in(char *str)
{
442
	TimeADT    *time;
443

444 445 446
	double		fsec;
	struct tm	tt,
			   *tm = &tt;
447

448 449 450 451 452
	int			nf;
	char		lowstr[MAXDATELEN + 1];
	char	   *field[MAXDATEFIELDS];
	int			dtype;
	int			ftype[MAXDATEFIELDS];
453

454
	if (!PointerIsValid(str))
455
		elog(ERROR, "Bad (null) time external representation", NULL);
456

457 458
	if ((ParseDateTime(str, lowstr, field, ftype, MAXDATEFIELDS, &nf) != 0)
		|| (DecodeTimeOnly(field, ftype, nf, &dtype, tm, &fsec) != 0))
459
		elog(ERROR, "Bad time external representation '%s'", str);
460

461
	if ((tm->tm_hour < 0) || (tm->tm_hour > 23))
462
		elog(ERROR, "Hour must be limited to values 0 through 23 in '%s'", str);
463
	if ((tm->tm_min < 0) || (tm->tm_min > 59))
464
		elog(ERROR, "Minute must be limited to values 0 through 59 in '%s'", str);
465
	if ((tm->tm_sec < 0) || ((tm->tm_sec + fsec) >= 60))
466
		elog(ERROR, "Second must be limited to values 0 through < 60 in '%s'", str);
467

468
	time = palloc(sizeof(TimeADT));
469

470
	*time = ((((tm->tm_hour * 60) + tm->tm_min) * 60) + tm->tm_sec + fsec);
471

472
	return time;
473
}	/* time_in() */
474 475


476
char *
477
time_out(TimeADT *time)
478
{
479 480 481
	char	   *result;
	struct tm	tt,
			   *tm = &tt;
482

483 484
	double		fsec;
	char		buf[MAXDATELEN + 1];
485

486 487
	if (!PointerIsValid(time))
		return NULL;
488

489 490 491
	tm->tm_hour = (*time / (60 * 60));
	tm->tm_min = (((int) (*time / 60)) % 60);
	tm->tm_sec = (((int) *time) % 60);
492

493
	fsec = 0;
494

495
	EncodeTimeOnly(tm, fsec, DateStyle, buf);
496

497
	result = palloc(strlen(buf) + 1);
498

499
	strcpy(result, buf);
500

501
	return result;
502
}	/* time_out() */
503 504 505


bool
506
time_eq(TimeADT *time1, TimeADT *time2)
507
{
508
	if (!PointerIsValid(time1) || !PointerIsValid(time2))
509
		return FALSE;
510

511
	return *time1 == *time2;
512
}	/* time_eq() */
513 514

bool
515
time_ne(TimeADT *time1, TimeADT *time2)
516
{
517
	if (!PointerIsValid(time1) || !PointerIsValid(time2))
518
		return FALSE;
519

520
	return *time1 != *time2;
521
}	/* time_eq() */
522 523

bool
524
time_lt(TimeADT *time1, TimeADT *time2)
525
{
526
	if (!PointerIsValid(time1) || !PointerIsValid(time2))
527
		return FALSE;
528

529
	return *time1 < *time2;
530
}	/* time_eq() */
531 532

bool
533
time_le(TimeADT *time1, TimeADT *time2)
534
{
535
	if (!PointerIsValid(time1) || !PointerIsValid(time2))
536
		return FALSE;
537

538
	return *time1 <= *time2;
539
}	/* time_eq() */
540 541

bool
542
time_gt(TimeADT *time1, TimeADT *time2)
543
{
544
	if (!PointerIsValid(time1) || !PointerIsValid(time2))
545
		return FALSE;
546

547
	return *time1 > *time2;
548
}	/* time_eq() */
549 550

bool
551
time_ge(TimeADT *time1, TimeADT *time2)
552
{
553
	if (!PointerIsValid(time1) || !PointerIsValid(time2))
554
		return FALSE;
555

556
	return *time1 >= *time2;
557
}	/* time_eq() */
558 559

int
560
time_cmp(TimeADT *time1, TimeADT *time2)
561
{
562
	return (*time1 < *time2) ? -1 : (((*time1 > *time2) ? 1 : 0));
563
}	/* time_cmp() */
564

565

566 567 568
/* datetime_time()
 * Convert datetime to time data type.
 */
569
TimeADT    *
570 571
datetime_time(DateTime *datetime)
{
572
	TimeADT    *result;
573 574 575 576 577 578 579
	struct tm	tt,
			   *tm = &tt;
	int			tz;
	double		fsec;
	char	   *tzn;

	if (!PointerIsValid(datetime))
580
		elog(ERROR, "Unable to convert null datetime to date", NULL);
581 582

	if (DATETIME_NOT_FINITE(*datetime))
583
		elog(ERROR, "Unable to convert datetime to date", NULL);
584 585 586 587 588 589 590 591 592 593 594 595 596 597

	if (DATETIME_IS_EPOCH(*datetime))
	{
		datetime2tm(SetDateTime(*datetime), NULL, tm, &fsec, NULL);

	}
	else if (DATETIME_IS_CURRENT(*datetime))
	{
		datetime2tm(SetDateTime(*datetime), &tz, tm, &fsec, &tzn);

	}
	else
	{
		if (datetime2tm(*datetime, &tz, tm, &fsec, &tzn) != 0)
598
			elog(ERROR, "Unable to convert datetime to date", NULL);
599 600
	}

601
	result = palloc(sizeof(TimeADT));
602 603 604

	*result = ((((tm->tm_hour * 60) + tm->tm_min) * 60) + tm->tm_sec + fsec);

605
	return result;
606
}	/* datetime_time() */
607 608


609
/* datetime_datetime()
610 611
 * Convert date and time to datetime data type.
 */
612
DateTime   *
613
datetime_datetime(DateADT date, TimeADT *time)
614
{
615
	DateTime   *result;
616

617 618
	if (!PointerIsValid(time))
	{
619
		result = palloc(sizeof(DateTime));
620
		DATETIME_INVALID(*result);
621 622 623
	}
	else
	{
624 625 626
		result = date_datetime(date);
		*result += *time;
	}
627

628
	return result;
629
}	/* datetime_datetime() */
630

631

632
int32							/* RelativeTime */
633
int4reltime(int32 timevalue)
634
{
635
	return timevalue;
636
}