Discussion:
datetime trunc
Maximiliano Robaina
2014-02-11 14:40:02 UTC
Permalink
Hi,

Is there an equivalent function to trunc datetime fields in firebird ?
Something like date_trunc in postgre [1]

--
Maxi

[1]  http://www.postgresql.org/docs/current/static/functions-datetime.html#FUNCTIONS-DATETIME-TRUNC
Virna Constantin
2014-02-11 15:06:20 UTC
Permalink
EXTRACT()http://www.firebirdsql.org/refdocs/langrefupd25-intfunc-extract.html



On Tuesday, February 11, 2014 4:42 PM, Maximiliano Robaina <***@yahoo.com.ar> wrote:





Hi,

Is there an equivalent function to trunc datetime fields in firebird ?
Something like date_trunc in postgre [1]

--
Maxi

[1]  http://www.postgresql.org/docs/current/static/functions-datetime.html#FUNCTIONS-DATETIME-TRUNC
Mark Rotteveel
2014-02-11 19:20:09 UTC
Permalink
Post by Virna Constantin
EXTRACT()
http://www.firebirdsql.org/refdocs/langrefupd25-intfunc-extract.html
Extract is not the same as PostgreSQL date_trunc. Extract returns the
requested value as an integer, while date_trunc returns a DATE or
TIMESTAMP truncated to the requested date field, example from
http://www.postgresql.org/docs/current/static/functions-datetime.html#FUNCTIONS-DATETIME-TRUNC
:

SELECT date_trunc('hour', TIMESTAMP '2001-02-16 20:38:40');
Result: 2001-02-16 20:00:00

SELECT date_trunc('year', TIMESTAMP '2001-02-16 20:38:40');
Result: 2001-01-01 00:00:00
Post by Virna Constantin
On Tuesday, February 11, 2014 4:42 PM, Maximiliano Robaina
Hi,
Is there an equivalent function to trunc datetime fields in firebird ?
Something like date_trunc in postgre [1]
------------------------------------

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Visit http://www.firebirdsql.org and click the Resources item
on the main (top) menu. Try Knowledgebase and FAQ links !

Also search the knowledgebases at http://www.ibphoenix.com

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Yahoo Groups Links

<*> To visit your group on the web, go to:
http://groups.yahoo.com/group/firebird-support/

<*> Your email settings:
Individual Email | Traditional

<*> To change settings online go to:
http://groups.yahoo.com/group/firebird-support/join
(Yahoo! ID required)

<*> To change settings via email:
firebird-support-***@yahoogroups.com
firebird-support-***@yahoogroups.com

<*> To unsubscribe from this group, send an email to:
firebird-support-***@yahoogroups.com

<*> Your use of Yahoo Groups is subject to:
http://info.yahoo.com/legal/us/yahoo/utos/terms/
Ismael L. Donis Garcia
2014-02-11 20:01:04 UTC
Permalink
SELECT CAST(CURRENT_TIMESTAMP as DATE) || ' ' || EXTRACT(HOUR FROM CURRENT_TIMESTAMP) || ':00:00' as date1
FROM RDB$DATABASE

Best Regard
========
| ISMAEL |
========

----- Original Message -----
From: Mark Rotteveel
To: firebird-***@yahoogroups.com
Sent: Tuesday, February 11, 2014 2:20 PM
Subject: Re: [firebird-support] datetime trunc
Post by Virna Constantin
EXTRACT()
http://www.firebirdsql.org/refdocs/langrefupd25-intfunc-extract.html
Extract is not the same as PostgreSQL date_trunc. Extract returns the
requested value as an integer, while date_trunc returns a DATE or
TIMESTAMP truncated to the requested date field, example from
http://www.postgresql.org/docs/current/static/functions-datetime.html#FUNCTIONS-DATETIME-TRUNC
:

SELECT date_trunc('hour', TIMESTAMP '2001-02-16 20:38:40');
Result: 2001-02-16 20:00:00

SELECT date_trunc('year', TIMESTAMP '2001-02-16 20:38:40');
Result: 2001-01-01 00:00:00
Post by Virna Constantin
On Tuesday, February 11, 2014 4:42 PM, Maximiliano Robaina
Hi,
Is there an equivalent function to trunc datetime fields in firebird ?
Something like date_trunc in postgre [1]
Alan McDonald
2014-02-11 20:54:28 UTC
Permalink
Cast the result of the extract as a date or timestamp.
Post by Mark Rotteveel
Post by Virna Constantin
EXTRACT()
http://www.firebirdsql.org/refdocs/langrefupd25-intfunc-extract.html
Extract is not the same as PostgreSQL date_trunc. Extract returns the
requested value as an integer, while date_trunc returns a DATE or
TIMESTAMP truncated to the requested date field, example from
http://www.postgresql.org/docs/current/static/functions-datetime.html#FUNCTIONS-DATETIME-TRUNC
SELECT date_trunc('hour', TIMESTAMP '2001-02-16 20:38:40');
Result: 2001-02-16 20:00:00
SELECT date_trunc('year', TIMESTAMP '2001-02-16 20:38:40');
Result: 2001-01-01 00:00:00
Post by Virna Constantin
On Tuesday, February 11, 2014 4:42 PM, Maximiliano Robaina
Hi,
Is there an equivalent function to trunc datetime fields in firebird ?
Something like date_trunc in postgre [1]
------------------------------------
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Visit http://www.firebirdsql.org and click the Resources item
on the main (top) menu. Try Knowledgebase and FAQ links !
Also search the knowledgebases at http://www.ibphoenix.com
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Yahoo Groups Links
Mark Rotteveel
2014-02-11 21:12:47 UTC
Permalink
Post by Alan McDonald
Cast the result of the extract as a date or timestamp.
That doesn't work and that is ok: I'd be surprised when casting the
result (= 20) of EXTRACT(hour from TIMESTAMP '2001-02-16 20:38:40')
would result in 2001-02-16 20:00:00, while the result (= 2001) of
EXTRACT(year FROM TIMESTAMP '2001-02-16 20:38:40') would result in
2001-01-01 00:00:00.

The TS asked for an equivalent function, there is none in Firebird,
although there is a workaround as demonstrated by Ismael.
Post by Alan McDonald
Post by Virna Constantin
EXTRACT()
http://www.firebirdsql.org/refdocs/langrefupd25-intfunc-extract.html
Extract is not the same as PostgreSQL date_trunc. Extract returns the
requested value as an integer, while date_trunc returns a DATE or
TIMESTAMP truncated to the requested date field, example from
http://www.postgresql.org/docs/current/static/functions-datetime.html#FUNCTIONS-DATETIME-TRUNC
SELECT date_trunc('hour', TIMESTAMP '2001-02-16 20:38:40');
Result: 2001-02-16 20:00:00
SELECT date_trunc('year', TIMESTAMP '2001-02-16 20:38:40');
Result: 2001-01-01 00:00:00
--
Mark Rotteveel


------------------------------------

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Visit http://www.firebirdsql.org and click the Resources item
on the main (top) menu. Try Knowledgebase and FAQ links !

Also search the knowledgebases at http://www.ibphoenix.com

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Yahoo Groups Links

<*> To visit your group on the web, go to:
http://groups.yahoo.com/group/firebird-support/

<*> Your email settings:
Individual Email | Traditional

<*> To change settings online go to:
http://groups.yahoo.com/group/firebird-support/join
(Yahoo! ID required)

<*> To change settings via email:
firebird-support-***@yahoogroups.com
firebird-support-***@yahoogroups.com

<*> To unsubscribe from this group, send an email to:
firebird-support-***@yahoogroups.com

<*> Your use of Yahoo Groups is subject to:
http://info.yahoo.com/legal/us/yahoo/utos/terms/
Loading...