Improve this answer. date_bin 9. 1) 2. This is a timestamp with time zone value which refers in fact to. 0) $$. In most databases, you can do this by converting to a date: select cast (time as date) as dte, sum (case when status = 1 then 1 else 0 end) as num_successful from t group by cast (time as date) order by dte; This assumes that 1 means "successful". What is the JPQL/JPA/Hibernate equivalent of the database function date_trunc('day', (entity. date_trunc ('day', TIMESTAMP '2001-02-16 20:38:40+00' AT TIME ZONE 'Australia/Sydney') HTH. Modified 10 years, 9 months ago. I edited my full query into my post now. If you want a date/time value (=timestamp) where the time part is 00:00:00 then you can use current_date::timestamp or date_trunc('day', current_timestamp). Use the date_trunc method to truncate off the day (or whatever else you want, e. For types without standard mathematical conventions (e. update foo set created_at = date_trunc('second', created_at) where created_at is not null; Instead of using date_trunc you can also just cast the value: created_at::timestamp(0) for the update statement, or current_timestamp::timestamp(0) in the default value. , and a timestamp. 3 Answers. DATE is an important data type that stores calendar dates in PostgreSQL. I can't seem to be able to translate the following query into SQLAlchemy. Syntax. 1. 5. 9. Truncate date in units other than default choices using date_trunc (Postgres 9. DATE_TRUNC. For example, if I have a table that looks like this. PostgreSQL provides a number of functions that return values related to the current date and time. SELECT date_trunc ('month', l_date) month FROM this_table GROUP BY month. I. PostGreSQL : date_trunc() returns timestamp with timezone when used on date. Now, Let us create index BTREE index on the created_at column. 1 Answer. 0, PostgreSQL 8. 600 is 10 minutes in seconds. , year, month, week from a date or time value. postgres=# SELECT NOW (); now ------------------------------- 2022-04-29. But I found that there's a trunc() function in pg_catalog. However, I am trying to do a select and ignore milliseconds. date_trunc('hour', timestamp '2001-02-16 20:38:40') → 2001-02-16 20:00:00. 2014-05-09 16:03:51 will be returned as 2014-05-01 00:00:00. "employees" AS "Employee" WHERE ("Employee". I'm trying to create quarterly average for player scores, however the default behaviour of postgres date_trunc('quarter', source) is that it starts first quarter with YYYY-01-01. 600. orm: dql: datetime_functions: date_trunc: YOUR_BUNDLE_HEREDoctrineExtensionsDateTrunc. For formatting functions, refer to Section 9. PostgreSQL provides a number of functions that return values related to the current date and time. Note that the latter returns a timestamp with time zone, not a timestamp value. The day (of the month) field (1 - 31). Checkout DoctrineExtensions. edited Aug 18, 2015 at 10:57. 1 Answer. Below query is working to get weekly summary. g. The problem is date_trunc('week', datetime_column) function considers Monday as the week start day and some of my customers user different start day in calendar (like Saturday). Alternatively you can use the date_trunc function: SELECT date_trunc ('day', my_date) Share. 9. I'm trying to create quarterly average for player scores, however the default behaviour of postgres date_trunc('quarter', source) is that it starts first quarter with YYYY-01-01. 9. (Values of type date and time are cast. Multiplying back effectively rounds down, achieving the same as. OpenSource Postgres. 2. Otherwise, the result has the same day component as date. 0. It looks like this: select date_trunc('month',now()). 4 and i noticed a strange behavior when using date_trunc. The output snippet shows that the DATE_PART() function pulls out the year from the given date. date_trunc¶. For formatting functions, refer to Section 9. I will get the same. Example 3:. (Values of type date and time are cast automatically to timestamp or interval, respectively. Date/Time Functions and Operators. In PostgreSQL, DATE_TRUNC Function is used to truncate a timestamp type or interval type with specific and high level of precision. 2. I will get the same. Author: John Naylor <john. So I have dates in a column for everyday with corresponding sales and I want to accumulate the sales for a week over a single date (say Friday). Thanks again! 👍 1. The date_trunc function truncates a TIMESTAMP or an INTERVAL value based on a specified date part e. date_part('month', interval '2 years 3 months') 3: date_trunc(text, timestamp) timestamp: 截断成指定的精度； date_trunc('hour', timestamp '2001-02-16 20:38:40') 2001-02-16 20:00:00: date_trunc(text, interval) interval: 截取指定的精度， date_trunc('hour', interval '2 days 3 hours 40 minutes') 2 days 03:00:00 SELECT the_date FROM date_trunc('day', timestamp with time zone '2001-01-1 00:00:00+0100') as the_date results to. The problem is we use Sunday as the first day of the week on our reports and PostgreSQL uses Monday as the. 876944') * 1000; would give. 30 illustrates the behaviors of the basic arithmetic operators ( +, *, etc. For. I have a table with a date field in timestamp format (ex: 2016-11-01 00:00:00). Also avoids misunderstandings general communication. If you're certain that column should always store only the first of a month, you should also use a CHECK constraint. If I use it like ths: select trunc(now(),'MM'). 1. date 、 time 、または timestamp を指定された精度に切り捨てます。. 4, PostgreSQL 9. I just sent a note about that to the pgsql-docs mailing list so hopefully it will be fixed soon. The following illustrates the syntax of the PostgreSQL TRUNC() function:. The LOCALTIME function takes one optional argument:. You can use the Now () function in PostgreSQL to display the current date. 0. Its return type is TIMESTAMP with TIMEZONE. date_trunc('hour', timestamp '2001-02-16 20:38:40') → 2001-02-16 20:00:00. In PostgreSQL, the date_trunc() function truncates a date/time value to a specified precision. When storing a date value, PostgreSQL uses the yyyy-mm-dd format e. ) This function takes two arguments. 0. Test case: SELECT (CAST (MAX (joindate) AS date) - CAST (MIN (joindate) AS date)) as DateDifference FROM generate_series ('2014-01-01'::timestamp, '2014-02-01'::timestamp, interval '1 hour') g. date_trunc always returns a timestamp, not a date. Tried via date_trunc. I am trying to pull entries which happen after April 1st, 2019. 9. Syntax: date_trunc (text, timestamp) Return Type: timestamp. To get week start and end date (as 0 for Monday and 4 for Friday): select cast (date_trunc ('week', current_date) as date) + 0 || '-->' || cast (date_trunc ('week', current_date) as date) + 4; 2015-08-17-->2015-08-21. date_trunc('month', current_timestamp) gives you the start of "this month" so in March this would be 2021-03-1 as the comparison for the upper limit is done using < it will include everything on the last day of February. end_date) >= DATE_TRUNC ('day', q. The field determines which date/time part will be extracted/pulled out from the given source. Delaying Execution. Next. Practical examples would include analyzing company’s quarterly. 这是 PostgreSQL date_trunc() 函数的语法： date_trunc ( field TEXT , source TIMESTAMP ) -> TIMESTAMP date_trunc ( field TEXT , source TIMESTAMPTZ , time_zone TEXT ) -> TIMESTAMPTZ date_trunc ( field TEXT , source INTERVAL ) -> INTERVAL In Postgresql, to truncate or extract the week of the timestamp value, pass the week as a string to the date_trunc function. date_trunc(text, timestamp) timestamp: Truncate to specified precision; see also Section 9. This function truncates a date/time value to a specified precision. 3. Select Current Quarter From Quarter Dates table. - The value for the field. 0. In Postgresql, dates are converted into strings using the CAST function. I think, what you want to do is: SELECT date (updated_at), count (updated_at) as total_count FROM "persons" WHERE ("persons". date_trunc. 9. The function is called time_bucket() and has the same syntax as the date_trunc() function but takes an interval instead of a time precision as first parameter. LOCALTIME(precision) Arguments. Here is a function that mimics postgres' DATE_TRUNC contract using the DATE_FORMAT mysql function that @Charles has recommended above. date_trunc('field', source) source is a value expression of type timestamp (values of type date and time are cast automatically). PostgreSQL provides a large number of functions and operators for the built-in data types. I tried date_trunc which does not have the precision I need. I want this to be just 2013-02-04. 2014-05-09 16:03:51 will be returned as 2014-05-01 00:00:00. date_trunc （ week 部分付き） 入力週を切り捨てて月曜日に開始します。 入力週を切り捨てて、定義された週の最初の日に開始します。 last_day （ week 部分付き） 入力週の日曜日を返します。 定義された週の最初の日からの相対的な入力週の最終日を返します。The DATE_TRUNC() method. Unless otherwise noted, operators shown as accepting. I think you are looking for the date_trunc () function, which is used to truncate timestamps. The second one which use DATE_TRUNC will tranc any date to the first day of the month. 27. However, date_trunc('day', created) is not equivalent to the other expressions, because it returns a timestamp value, not a date. 9. com> Reviewed-by: Tom Lane. Gordon Linoff went further in his. ). getCriteriaBuilder (); CriteriaQuery<Date> query = cb. CAST both fields to datatype DATE and you can use a minus: (CAST (MAX (joindate) AS date) - CAST (MIN (joindate) AS date)) as DateDifference. 2. postgres =# select extract (epoch from '2023-09-05 12:00:00':: timestamp); date_part-----1693915200 Register as a new user and use Qiita more conveniently You get articles that match your needsIn existing versions of Postgres, you can use arithmetic: select t. Current Date/Time. Input Format: Dates in yellow are the dates to aggregate sales on. Does date_trunc automatically work on current year when used with CURRENT_DATE? Yes, date_trunc('month', CURRENT_DATE) will truncate the current date. For this purpose, specify the “MONTH” as the first argument to any of the functions mentioned above and then use the GROUP BY clause. date_trunc(field, source [, time_zone ]) source is a value expression of type timestamp, timestamp with time zone, or interval. date) going over the date/time functions in. 1 min read. or you can create your own. If so, use date_trunc(): select date_trunc('month', order_date) as yyyymm If you really want a string, you should accept Nick's answer. testdb=# SELECT date_trunc('hour', TIMESTAMP '2001-02-16 20:38:40'); date_trunc ----- 2001-02-16 20:00:00 (1 row) testdb=# SELECT date_trunc('year', TIMESTAMP '2001-02-16 20:38:40'); date_trunc. Now, let us see the Date/Time operators and Functions. timestamp '2001-09-29 03:00' - timestamp '2001-09-27 12:00'. For example. ADVERTISEMENT. Translate to PostgreSQL generate_series #2144. Follow answered Feb 26, 2018 at 23:30. 300) must add 10 minutes and collect all the line that are within this time interval, or , all records that are between 19:18:00. When dealing with dates, it accepts as a parameter a Template Pattern for Date/Time (see link above) then a timestamp, and returns a timestamp. For example I need to get number of sales each week. 4. These SQL-standard functions all return values based on. 0 did not follow the conventional numbering of millennia, but just returned the year field divided by 1000. Postgres has date_trunc which operates on timestamp or interval, and: Values of type date and time are cast automatically to timestamp or interval, respectively. E. ). However, you can set the time portion of a timestamp, dropping the date portion entirely with date_trunc. The basic syntax of the DATE_TRUNC function is as shown below:. Related: Ignoring time zones altogether in Rails and PostgreSQL;I need to query for a date like the one in my code, and in postgreSQL i found date_trunc to "cut off" unnecessary information from the date. DATE_PART () is a built-in function in PostgreSQL that is used to extract/retrieve a specific part (like a month, year, hour, minutes, etc. Table 10-4. Table 9-26 illustrates the behaviors of the basic arithmetic operators ( +, *, etc. PostgreSQL provides a number of functions that return values related to the current date and time. morland@gmail. PostgreSQL provides a number of functions that return values related to the current date and time. (Values of type date and time are cast automatically to timestamp or interval, respectively. 9. 9. What could be going wrong here. Use the DATE_TRUNC() function if you want to retrieve a date or time with a specific precision from a PostgreSQL database. The syntax of the function is as follows: DATE_TRUNC ('precision', expression); where expression is a timestamp or an interval to truncate. Trimming trailing :00 from output after date_trunc. Current Date/Time. – zhrist. *, (first_week + ( (date - first_week::date) / 14)*14 * interval '1 day')::date as biweek from (select t. The function date_trunc is conceptually similar to the trunc function for numbers. Or simpler, use the column number: group by 1 (if the expression is the first column in the select clause). - Return Type: TIMESTAMP. g. To get week start and end date (as 0 for Monday and 4 for Friday): select cast (date_trunc ('week', current_date) as date) + 0 || '-->' || cast (date_trunc ('week', current_date) as date) + 4; 2015-08-17-->2015-08-21. Postgres, Update TIMESTAMP to current date but. If you want to take the current date as the first argument, you can use the following form of the AGE() function: AGE(timestamp); Code language: SQL (Structured Query Language) (sql) For example, if someone has a birth date2000-01-01 and the current date is 2017-03-20, his/her age will be:We are using date_trunc, group by, and aggregate functions to retrieve table data as per day basis in PostgreSQL, we are using date_trunc function on the column from which we are retrieving data as per day basis. I'm trying to create a string with the week number and the first and last date on that week, like this: 'W41 04/10-10/10' I was able to create a formula on Google Sheets that solve my problem and now I need to do this on PostgreSQL. Functions and Operators. The Timescale extension for PostgreSQL gives the ability to group by arbitrary time intervals. How to use the PostgreSQL Date Function: Date_Trunc. 2. 9. date_trunc can be really helpful if you want to roll up time fields and count by day or month. Then the following WHERE clause can be used 9as was shown above: WHERE time >= date_trunc ('hour', now ()) - INTERVAL '1 hour' AND time < (date_trunc ('hour', now ())) However to work with our. 9. 1 Answer. The DATE_TRUNC () function is used to truncate a date, time, or timestamp to a specified interval, such as the day, week, or month, in PostgreSQL and SQL Server. You see the last two colums being blank, date_trunc returns null. Example: PostgreSQL DATE_TRUNC() function : Example: Code: SELECT date_trunc('hour', timestamp. 11. 31 illustrates the behaviors of the basic arithmetic operators ( +, *, etc. ). 8) Postgres DATE_TRUNC() Function. 1 st 1970 in your time zone, use. date_trunc is only defined for timestamp with time zone and timestamp inputs. but it's greatly complicated by the nature of your data. dateoftransfer::date)::Date from table_withdates; --returns 2005-10-01. 7. You can use date_trunc function to round data value to the first day of the week. Don't forget to keep the timezone in mind. 4 shows the mathematical operators that are available for the standard numeric types. Let’s add a year to any date. Follow. Cloud SQL Fully managed database for MySQL, PostgreSQL, and SQL Server. Follow. the_date 2000-12-31 00:00 Is there a way to tell date_trunc to do day/month/year conversions based on the timezone it is feeded with? The expected output would be: 2001-01-1 00:00+0100 With PostgreSQL there are a number of date/time functions available, see here. SELECT my_date::date::timestamp. reg = 'PH-BVA' GROUP BY 1, "PositionReport". Note there can be ambiguity in the months returned by age because different months have a different number of days. 2: date_trunc('hour', timestamp '2001-02-16 20:38:40') 2001-02-16 20:00:00. Unless otherwise noted, operators shown as. Note that the specifier is a string and needs to be enclosed in quotes. In this case, it is used to truncate the result of the subtraction operation to seconds. Syntax: DATE_PART (field, source) In the above syntax the field is an identifier that is used to set the field to extract the data from the source. For formatting functions, refer to Section 9. My current work around is to map date_trunc as a function and explicitly call it but it seems odd to have to do that. g. The TRUNC function has the signature:. date AT TIME ZONE 'UTC'))? I need to be rounding down to full days (and weeks, etc. Use the PostgreSQL AGE () function to retrieve the interval between two timestamps or dates. ex: between 2013-04-07 15:30:00, 2013-04-07 15:40:00 5 results. If the value is negative, the parts are counted backward from the end of the string. select count(*) as logged_users, EXTRACT(hour from login_time::timestamp) as Hour from loginhistory where login_time::date = '2021-04-21' group by Hour order by Hour;. 8. Remove the longest string that contains specified characters from the right of the input string. ac. Trimming trailing :00 from output after date_trunc. ) field selects to which precision to truncate the input value. The most frequently used Postgres date functions and business scenarios where they come in handy: Rounding off timestamps with DATE_TRUNC function. date_trunc() "rounds" the value to the specified precision. date=to_char (date_trunc ('day', se. This is a timestamp with time zone value which refers in fact to 23:59:59 on sunday, but with 2 hours of difference with UTC time, depends on your locale and settings. 9. Table 9. SELECT date_trunc($1, purchase_date) unit_of_time, SUM(total) FROM orders WHERE purchase_date >= $2 AND purchase_date <= $3 GROUP BY unit_of_time ORDER BY unit_time; [interval, startDate, endDate] The above query works correctly for when I pass in either 'month' or 'day' as the interval variable, but gives incorrect values. timestamp)) from rollup_days as rp; To convert the timestamp back to a bigint, use extract ()Select date_trunc('week',dateTime) Date_week, Max(Ranking) Runing_Total_ID from (select datetime, id , dense_rank over (order by datetime) as Ranking from Table1) group by 1 This query is working for me to give me the running total of total IDs by week. Let’s see the following example. How to DATE_TRUNC by 10 days. Read more about PostgreSQL and time series right now: my blog post about using string encoding to find patterns in timeseries has further. Popular Course in this category. milliseconds. 8) Postgres DATE_TRUNC() Function. ShareTeams. date_trunc¶. Or simpler, use the column number: group by 1 (if the expression is the first column in the select clause). Friday afternoon and I'm fried. What you should do: Order by year and month. You can use the Now () function in PostgreSQL to display the current date and time without any mention of milliseconds. 5 introduced a feature called block range indexes (aka BRIN ) that is incredibly helpful in efficiently searching over large time series data and has the benefit of taking up significantly less space on disk than a standard B-tree index. century. Truncate to specified precision; see Section 9. About;. In PostgreSQL I am extracting hour from the timestamp using below query. 1) 2. CURRENT_DATE: DATE: Return the current date: CURRENT_TIME: TIMESTAMPTZ: Return the current time: CURRENT_TIMESTAMP: TIMESTAMPTZ: Return the current date and time with time zone at which the current transaction starts: DATE_PART: DOUBLE PRECISION: Get a field of a timestamp or an interval e. In PostgreSQL, DATE_TRUNC Function is used to truncate a timestamp type or interval type with specific and high level of precision. select * from table where extract (hour from column1) in (8, 9) where cast (column1 as time) >= '8:00' and column1::time < '10:00'. PostgreSQL provides a number of functions that return values related to the current date and time. date, count (se. I'm able to recreate this issue in a new clean Docker-TimescaleDB-container (:latest-pg12), but not in an otherwise equivalent PostgreSQL-12. gradovenko mentioned this issue on Dec 7, 2021. 000000の場合Postgres has plenty of date-specific functions -- from date_trunc() to age(). PostgreSQL date_part function will allow retrieving subfields from the date and time value, e. first day of year + current week * 7 days = closest preceding date with same day of week as the first day of the year. 说明：DATE_TRUNC 函数根据您指定的日期部分（如小时、周或月）截断时间戳表达式或文本。DATE_TRUNC 返回指定的年的第一天、指定的月的第一天或指定的周的星期一。. Oct 27, 2013 at 12:41. atZone (ZoneId. (Tried with LIKE too and same outcome). PostgreSQL. EXTRACT. 0 did not follow the conventional numbering of centuries, but just returned the year field divided by 100. I need it to return april 22. Slobodan Pejic Slobodan Pejic. Related: PostgreSQL: between with datetime2,521 20 21. Mathematical operators are provided for many PostgreSQL types. So fellow SQL aficionado's how to take the following WHERE clause in PostgreSQL and convert it to SQLite3 without using a compiled extension: WHERE DATE_TRUNC ('day', c. edited Aug 18, 2015 at 10:57. Need group data by each line time interval, e. These SQL-standard functions all return values based on the start. You should be familiar with the background information on date/time data types from. Getting results between two dates in PostgreSQL. Code:The date/time functions provide a powerful set of tools for manipulating various date/time types. l_date is the column where I would pull the date from. decade. This gives the date rounded to the start of the quarter, e. 基本的な使い方を見ていこう。. PostgreSQL provides a number of functions that return values related to the current date and time. create function end_of_month(date) returns date as $$ select (date_trunc('month', $1) + interval '1 month' - interval '1 day')::date; $$ language 'sql' immutable strict; EDIT Postgres 11+ Pulling this out of the comments from @Gabriel , you can now combine interval expressions in one interval (which makes things a little shorter):I think the :: operator is more common in "Postgres land". As you don't want to include rows from "this" month, you also need to add a condition for that. "updated_at" BETWEEN '2012-10-17 00:00:00. In your example, you could use: SELECT * FROM myTable WHERE date_trunc('day', dt) = 'YYYY-MM-DD'; If you are running this query regularly, it is possible to create an index using the date_trunc function as well: The precision values are a subset of the field identifiers that can be used with the EXTRACT() and DATE_PART() functions. See Postgres Date/Time Functions and Operators for more infoIn PostgreSQL, the DATE_TRUNC function is used to truncate a date, time, or timestamp value to a specified precision. Its type is timestamp without time zone. 2. Delaying Execution. date dollars 2016-10-03 1 2016-10-05 1 2016-10-10 1 2016-10-17 2 2016-10-24 2date_trunc PostgreSQL function equal for mySQL. In Postgresql, we can also add a year to the current date using the INTERVAL data type. Isolating hour-of-day and day-of-week with EXTRACT function. DATE_TRUNC는 타임스탬프 값을 받아서, 특정 단위 밑을 잘라버리는 함수다. In PostgreSQL, DATE_TRUNC () is a built-in date function that truncates/trims the unnecessary part from the date/time. That is easy enough to add. 例1：now()=2023-04-18 00:00:00. 指定した単位（month）以下の値が切り捨てられ、 年 月という結果. PostgreSQL Version: 9. values date_trunc ('MONTH', DATE ('2007-02-18')) Result: 2007-02-01 00:00:00. Sorted by: 3. sslaws mentioned this issue on May 2, 2022. Summary: in this tutorial, we will introduce you to the PostgreSQL DATE_PART() function that allows you to retrieve subfields e. I am wondering if it's possible to truncate dates other than using the default choices using date_trunc. Table 9-28 shows the available functions for date/time value processing, with details appearing in the following subsections. 2: date_trunc('hour', timestamp '2001-02-16 20:38:40') 2001-02-16 20:00:00:. PostgreSQL - DATE/TIME Functions and Operators. , are used to compare the dates in Postgres. I have TableA and it has a field of time_captured | timestamp without time zone | default now () It is being used to record when data was inserted into the table. Issue in creating a function in PostgreSQL using date_trunc. Basically, there are two parameters we. day. Sorted by: 3. ex: between 2013-04-07 15:30:00, 2013-04-07 15:40:00 5 results. These SQL-standard functions all return values based on. I want to implement R's ceiling_date fucntion in SQL (Postgresql). CREATE TABLE log ( log_id SERIAL PRIMARY KEY, message VARCHAR ( 255) NOT NULL , created_at TIME DEFAULT. Pictorial Presentation of PostgreSQL DATE_TRUNC() function. The. The PostgreSQL TRUNC() function returns a number truncated to a whole number or truncated to the specified decimal places. (Values of type date and time are cast automatically to timestamp or interval, respectively. In time-series analysis, the granularity could be on intervals of years, quarters, months, weeks, days, or hours. start }}'::timestamp) The result of that is a timestamp from which you can subtract the interval:. Is there a way I could update based on a date range in a postgres DB? Meaning if I have a row with following values, name code some_timestamp abc 1 2020-09-07T13:22:23. . day. The precision values are a subset of the field identifiers that can be used with the EXTRACT() and DATE_PART() functions. when querying the data, explain shows that all partitions are being queried when I'm constructing a date with date functions, whereas when I use hard coded dates only the targeted partitions are being scanned. PostgreSQL database has a default time zone setting, the operating system’s time zone. It will return the date truncated to month precision, e. It can also return a number truncated to the whole number if no precision is defined. The date part to which to truncate the timestamp value. The snippet provided below shows how to use the DATE_TRUNC () function in Postgres: DATE_TRUNC (dateField, timestamp); Specify the date field, such as year, month, day, etc. 7) PostgreSQL Now (): Display without milliseconds. It's not immutable because it depends on the sessions time zone setting. to_char and all of the formatting functions let you query time however you want. In PostgreSQL, DATE_TRUNC() is a built-in date function that truncates/trims the unnecessary part from the date/time. The. 2. 2. , hour, week, or month and returns the truncated timestamp or interval with a level of precision. , year, month, week from a date or time value. SELECT to_char (date_trunc ('month', date), 'YYYY') AS year, to_char (date_trunc ('month', date), 'Mon') AS month, to_char (date_trunc ('month', date), 'MM') AS month_number, sum (duration) AS monthly_sum FROM timesheet GROUP BY date_trunc ('month', date); From a. – zhrist. Postgres has date_trunc which operates on timestamp or interval, and: Values of type date and time are cast automatically to timestamp or interval, respectively. ·. The real usefu. Test case: SELECT (CAST (MAX (joindate) AS date) - CAST (MIN (joindate) AS date)) as DateDifference FROM generate_series ('2014-01-01'::timestamp, '2014-02-01'::timestamp, interval '1 hour') g.