![]() ![]() Across daylight saving time changes (when the session time zone is set to a time zone that recognizes DST), this means interval '1 day' does not necessarily equal interval '24 hours'. When adding an interval value to (or subtracting an interval value from) a timestamp with time zone value, the days component advances or decrements the date of the timestamp with time zone by the indicated number of days, keeping the time of day the same. SELECT (DATE '', INTERVAL '100 days') OVERLAPS This means for instance that two time periods with only an endpoint in common do not overlap. ![]() Each time period is considered to represent the half-open interval start <= time < end, unless start and end are equal in which case it represents that single time instant. When a pair of values is provided, either the start or the end can be written first OVERLAPS automatically takes the earlier value of the pair as the start. The endpoints can be specified as pairs of dates, times, or time stamps or as a date, time, or time stamp followed by an interval. This expression yields true when two time periods (defined by their endpoints) overlap, false when they do not overlap. ( start1, length1) OVERLAPS ( start2, length2) In addition to these functions, the SQL OVERLAPS operator is supported: ( start1, end1) OVERLAPS ( start2, end2) Make_timestamptz(2013, 7, 15, 8, 15, 23.5)Ĭurrent date and time (start of current statement) see Section 9.9.4Ĭurrent date and time (like clock_timestamp, but as a text string) see Section 9.9.4Ĭonvert Unix epoch (seconds since 00:00:00+00) to timestamp Make_timestamptz( year int, month int, day int, hour int, min int, sec double precision, )Ĭreate timestamp with time zone from year, month, day, hour, minute and seconds fields if timezone is not specified, the current time zone is used Make_timestamp( year int, month int, day int, hour int, min int, sec double precision)Ĭreate timestamp from year, month, day, hour, minute and seconds fields ![]() Make_time( hour int, min int, sec double precision)Ĭreate time from hour, minute and seconds fields Make_interval( years int DEFAULT 0, months int DEFAULT 0, weeks int DEFAULT 0, days int DEFAULT 0, hours int DEFAULT 0, mins int DEFAULT 0, secs double precision DEFAULT 0.0)Ĭreate interval from years, months, weeks, days, hours, minutes and seconds fields Justify_interval(interval '1 mon -1 hour')Ĭreate date from year, month and day fields ![]() Test for finite time stamp (not +/-infinity)Īdjust interval so 30-day time periods are represented as monthsĪdjust interval so 24-hour time periods are represented as daysĪdjust interval using justify_days and justify_hours, with additional sign adjustments Truncate to specified precision see also Section 9.9.2ĭate_trunc('hour', timestamp ' 20:38:40')ĭate_trunc('hour', interval '2 days 3 hours 40 minutes')Įxtract(month from interval '2 years 3 months') Get subfield (equivalent to extract) see Section 9.9.1ĭate_part('month', interval '2 years 3 months') Subtract arguments, producing a "symbolic" result that uses years and months, rather than just daysĬurrent date and time (changes during statement execution) see Section 9.9.4Ĭurrent date and time (start of current transaction) see Section 9.9.4 Interval '1 hour' / double precision '1.5' Also, the + and * operators come in commutative pairs (for example both date + integer and integer + date) we show only one of each such pair.ĭouble precision '3.5' * interval '1 hour' For brevity, these variants are not shown separately. Similarly, a date value is assumed to represent midnight in the TimeZone zone when comparing it to a timestamp.Īll the functions and operators described below that take time or timestamp inputs actually come in two variants: one that takes time with time zone or timestamp with time zone, and one that takes time without time zone or timestamp without time zone. When comparing a timestamp without time zone to a timestamp with time zone, the former value is assumed to be given in the time zone specified by the TimeZone configuration parameter, and is rotated to UTC for comparison to the latter value (which is already in UTC internally). Dates and timestamps (with or without time zone) are all comparable, while times (with or without time zone) and intervals can only be compared to other values of the same data type. In addition, the usual comparison operators shown in Table 9-1 are available for the date/time types. You should be familiar with the background information on date/time data types from Section 8.5. For formatting functions, refer to Section 9.8. Table 9-29 illustrates the behaviors of the basic arithmetic operators ( +, *, etc.). Table 9-30 shows the available functions for date/time value processing, with details appearing in the following subsections. ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |