pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Date Time Functions - ANSI SQL ?
5+ messages / 4 participants
[nested] [flat]

* Date Time Functions - ANSI SQL ?
@ 2001-07-30 04:19  Gonzo Rock <GonzoRock@Excite.com>
  0 siblings, 2 replies; 5+ messages in thread

From: Gonzo Rock @ 2001-07-30 04:19 UTC (permalink / raw)
  To: pgsql-sql

Curious to know...

Are all the date time functions described in the pgSQL docs are ANSI-SQL or pgSQL extensions?

For Example in the Docs:
------------------------
4.7.1. EXTRACT, date_part

EXTRACT (field FROM source)

The extract function retrieves sub-fields from date/time values, such as year or hour. source is a value expression that evaluates to type timestamp or interval. (Expressions of type date or time will be cast to timestamp and can therefore be used as well.) field is an identifier (not a string!) that selects what field to extract from the source value. The extract function returns values of type double precision. The following are valid values: 

day
  The day (of the month) field (1 - 31) 

  SELECT EXTRACT(DAY FROM TIMESTAMP '2001-02-16 20:38:40');
  Result: 16

Thanks all,




^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: Date Time Functions - ANSI SQL ?
@ 2001-07-30 05:39  Tom Lane <tgl@sss.pgh.pa.us>
  parent: Gonzo Rock <GonzoRock@Excite.com>
  1 sibling, 0 replies; 5+ messages in thread

From: Tom Lane @ 2001-07-30 05:39 UTC (permalink / raw)
  To: Gonzo Rock <GonzoRock@Excite.com>; +Cc: pgsql-sql

Gonzo Rock <GonzoRock@Excite.com> writes:
> Are all the date time functions described in the pgSQL docs are
> ANSI-SQL or pgSQL extensions?

One or the other, yes ;-)

EXTRACT() is ANSI, though I think we may accept a few more field keywords
than the spec mentions.

			regards, tom lane



^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: Date Time Functions - ANSI SQL ?
@ 2001-07-30 17:39  Gonzo Rock <GonzoRock@Excite.com>
  parent: Gonzo Rock <GonzoRock@Excite.com>
  1 sibling, 1 reply; 5+ messages in thread

From: Gonzo Rock @ 2001-07-30 17:39 UTC (permalink / raw)
  To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-sql

At 01:39 AM 7/30/01 -0400, Tom Lane wrote:
>Gonzo Rock <GonzoRock@Excite.com> writes:
>> Are all the date time functions described in the pgSQL docs are
>> ANSI-SQL or pgSQL extensions?
>
>One or the other, yes ;-)

Hey Tom,

You Clever guy!

Yes, One or the other!

OK OK! Nothing worse than a programmer who can't be explicit... maybe that's why I struggle so much ;-)

QUESTION:
I'm trying to find a pgSQL source that documents the Non-Standard pgSQL stuff, the stuff that will break when attempting to execute against mySQL/Oracle/MSSQLServer etc...

Immediately: What about the INHERITANCE capabilities... pretty cool but scarry at the same time. (can you tell I'm brand new to SQL and six months late on my current SQL project?)

thanks again,



^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: Date Time Functions - ANSI SQL ?
@ 2001-08-05 21:41  Peter Eisentraut <peter_e@gmx.net>
  parent: Gonzo Rock <GonzoRock@Excite.com>
  0 siblings, 1 reply; 5+ messages in thread

From: Peter Eisentraut @ 2001-08-05 21:41 UTC (permalink / raw)
  To: Gonzo Rock <GonzoRock@Excite.com>; +Cc: pgsql-sql

Gonzo Rock writes:

> I'm trying to find a pgSQL source that documents the Non-Standard
> pgSQL stuff, the stuff that will break when attempting to execute
> against mySQL/Oracle/MSSQLServer etc...

While it would be an appreciated effort to create such a document (and we
already try to document standards-compliance in other places), it would be
of less practical use than it might seem at first.  Most SQL database
packages implement all kinds of functions that are found nowhere near the
SQL standard.  PostgreSQL has copied a number of these and added aliases
and near-aliases in a number of other cases.  We don't all program in ANSI
C or C++ these days either.  What is portable is a bit of a matter of
experience and research, modulo good code organization so you can replace
the unportable parts easily.

-- 
Peter Eisentraut   peter_e@gmx.net   http://funkturm.homeip.net/~peter




^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: Re: Date Time Functions - ANSI SQL ?
@ 2001-08-05 22:12  Josh Berkus <josh@agliodbs.com>
  parent: Peter Eisentraut <peter_e@gmx.net>
  0 siblings, 0 replies; 5+ messages in thread

From: Josh Berkus @ 2001-08-05 22:12 UTC (permalink / raw)
  To: Gonzo Rock <GonzoRock@Excite.com>; +Cc: pgsql-sql

Gonzo,

> > I'm trying to find a pgSQL source that documents the Non-Standard
> > pgSQL stuff, the stuff that will break when attempting to execute
> > against mySQL/Oracle/MSSQLServer etc...

Almost anything you port will break MS SQL Server (7.0 and 6.5, anyway).
SQL Server is so far off the ANSI standard for Dates & Times that no
application may be ported from another server which relies on dates and
time manipulation.

FYI.

-Josh Berkus


______AGLIO DATABASE SOLUTIONS___________________________
                                       Josh Berkus
  Complete information technology      josh@agliodbs.com
   and data management solutions       (415) 565-7293
  for law firms, small businesses        fax 621-2533
    and non-profit organizations.      San Francisco







^ permalink  raw  reply  [nested|flat] 5+ messages in thread


end of thread, other threads:[~2001-08-05 22:12 UTC | newest]

Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2001-07-30 04:19 Date Time Functions - ANSI SQL ? Gonzo Rock <GonzoRock@Excite.com>
2001-07-30 05:39 ` Tom Lane <tgl@sss.pgh.pa.us>
2001-07-30 17:39 ` Gonzo Rock <GonzoRock@Excite.com>
2001-08-05 21:41   ` Peter Eisentraut <peter_e@gmx.net>
2001-08-05 22:12     ` Josh Berkus <josh@agliodbs.com>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox