Обсуждение: timestamp no fractional seconds
I need to create a table with two columns of type timestamp but I
don't want to store any fractional part of the seconds field. So,
I created a table with:
CREATE TABLE timeclock (
timeclock_id SERIAL,
employee_id INTEGER,
clockin TIMESTAMP[0] NOT NULL,
clockout TIMESTAMP[0] DEFAULT NULL,
PRIMARY KEY (timeclock_id),
FOREIGN KEY (employee_id)
REFERENCES employee
ON DELETE RESTRICT
ON UPDATE CASCADE
);
But now I can no longer insert a timestamp as I normally would:
gms=# insert into timeclock (employee_id,clockin,clockout) values(3169,now(),null);
ERROR: column "clockin" is of type timestamp without time zone[] but expression is of type timestamp with time zone
LINE 1: insert into timeclock (employee_id,clockin,clockout) values(...
^
HINT: You will need to rewrite or cast the expression.
If I cast it, I get something really strange:
gms=# insert into timeclock (employee_id,clockin,clockout) values(3169,now()::timestamp,null);
ERROR: column "clockin" is of type timestamp without time zone[] but expression is of type timestamp without time
zone
LINE 1: insert into timeclock (employee_id,clockin,clockout) values(...
^
HINT: You will need to rewrite or cast the expression.
Am I creating the table correctly? If so, how do I insert or update
rows?
Thanks.
--
Brandon
On Tue, Jun 02, 2009 at 11:26:05AM -0500, Brandon Metcalf wrote: > Am I creating the table correctly? If so, how do I insert or update > rows? timestamp[] is array of timestamps. there is no way to make timestamps "without subsecond data". you can add trigger to remove unneeded parts of the data, or (much better) strip it while selecting data - for example, using to_char() function. Best regards, depesz -- Linkedin: http://www.linkedin.com/in/depesz / blog: http://www.depesz.com/ jid/gtalk: depesz@depesz.com / aim:depeszhdl / skype:depesz_hdl / gg:6749007
b == brandon@geronimoalloys.com writes:
b> I need to create a table with two columns of type timestamp but I
b> don't want to store any fractional part of the seconds field. So,
b> I created a table with:
b> CREATE TABLE timeclock (
b> timeclock_id SERIAL,
b> employee_id INTEGER,
b> clockin TIMESTAMP[0] NOT NULL,
b> clockout TIMESTAMP[0] DEFAULT NULL,
b> PRIMARY KEY (timeclock_id),
b> FOREIGN KEY (employee_id)
b> REFERENCES employee
b> ON DELETE RESTRICT
b> ON UPDATE CASCADE
b> );
...
b> Am I creating the table correctly? If so, how do I insert or update
b> rows?
I suppose my question really boils down to how do I cast the timestamp
with no fractional seconds part? For example, if I have a table where
I haven't put a limit on the fractional seconds part, how would I
select the timestamp without fractional seconds?
gms=> select clockin from timeclock;
clockin
----------------------------
2009-06-02 11:34:21.314392
(1 row)
gms=> select clockin::timestamp without time zone[0] from timeclock;
ERROR: cannot cast type timestamp without time zone to timestamp without time zone[]
LINE 1: select clockin::timestamp without time zone[0] from timecloc...
^
gms=> select clockin::timestamp[0] from timeclock;
ERROR: cannot cast type timestamp without time zone to timestamp without time zone[]
LINE 1: select clockin::timestamp[0] from timeclock;
--
Brandon
Hello use timestamp(0) timestamp[0] means array of timestamps regards Pavel Stehule 2009/6/2 Brandon Metcalf <brandon@geronimoalloys.com>: > I need to create a table with two columns of type timestamp but I > don't want to store any fractional part of the seconds field. So, > I created a table with: > > CREATE TABLE timeclock ( > timeclock_id SERIAL, > employee_id INTEGER, > clockin TIMESTAMP[0] NOT NULL, > clockout TIMESTAMP[0] DEFAULT NULL, > > PRIMARY KEY (timeclock_id), > > FOREIGN KEY (employee_id) > REFERENCES employee > ON DELETE RESTRICT > ON UPDATE CASCADE > ); > > But now I can no longer insert a timestamp as I normally would: > > gms=# insert into timeclock (employee_id,clockin,clockout) values(3169,now(),null); > ERROR: column "clockin" is of type timestamp without time zone[] but expression is of type timestamp with time zone > LINE 1: insert into timeclock (employee_id,clockin,clockout) values(... > ^ > HINT: You will need to rewrite or cast the expression. > > If I cast it, I get something really strange: > > gms=# insert into timeclock (employee_id,clockin,clockout) values(3169,now()::timestamp,null); > ERROR: column "clockin" is of type timestamp without time zone[] but expression is of type timestamp without time zone > LINE 1: insert into timeclock (employee_id,clockin,clockout) values(... > ^ > HINT: You will need to rewrite or cast the expression. > > Am I creating the table correctly? If so, how do I insert or update > rows? > > Thanks. > > > -- > Brandon > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general >
2009/6/2 Brandon Metcalf <brandon@geronimoalloys.com>: > b == brandon@geronimoalloys.com writes: > > b> I need to create a table with two columns of type timestamp but I > b> don't want to store any fractional part of the seconds field. So, > b> I created a table with: > > b> CREATE TABLE timeclock ( > b> timeclock_id SERIAL, > b> employee_id INTEGER, > b> clockin TIMESTAMP[0] NOT NULL, > b> clockout TIMESTAMP[0] DEFAULT NULL, > > b> PRIMARY KEY (timeclock_id), > > b> FOREIGN KEY (employee_id) > b> REFERENCES employee > b> ON DELETE RESTRICT > b> ON UPDATE CASCADE > b> ); > > ... > > b> Am I creating the table correctly? If so, how do I insert or update > b> rows? > > > I suppose my question really boils down to how do I cast the timestamp > with no fractional seconds part? For example, if I have a table where > I haven't put a limit on the fractional seconds part, how would I > select the timestamp without fractional seconds? > > > gms=> select clockin from timeclock; > > clockin > ---------------------------- > 2009-06-02 11:34:21.314392 > (1 row) > > gms=> select clockin::timestamp without time zone[0] from timeclock; > ERROR: cannot cast type timestamp without time zone to timestamp without time zone[] > LINE 1: select clockin::timestamp without time zone[0] from timecloc... > ^ > gms=> select clockin::timestamp[0] from timeclock; > ERROR: cannot cast type timestamp without time zone to timestamp without time zone[] > LINE 1: select clockin::timestamp[0] from timeclock; > > > -- > Brandon > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general > postgres=# select now()::timestamp(0); now --------------------- 2009-06-02 18:42:41 (1 row) Time: 173,165 ms postgres=# regards Pavel
p == pavel.stehule@gmail.com writes: p> Hello p> use timestamp(0) p> timestamp[0] means array of timestamps Of course. I was reading the documentation wrong and taking the [] as literal instead of it meaning an optional parameter as it always does. Thanks. -- Brandon
Brandon Metcalf <brandon@geronimoalloys.com> writes:
> I suppose my question really boils down to how do I cast the timestamp
> with no fractional seconds part? For example, if I have a table where
> I haven't put a limit on the fractional seconds part, how would I
> select the timestamp without fractional seconds?
Cast to timestamp(0) ... which is something entirely different
from timestamp[0].
regards, tom lane
=?UTF-8?Q?Grzegorz_Ja=C5=9Bkiewicz?= <gryzman@gmail.com> writes: > what difference does the (0) make than ? is timestamp() a function than ?/ No, it's a type. See http://www.postgresql.org/docs/8.3/static/datatype-datetime.html regards, tom lane
what difference does the (0) make than ? is timestamp() a function than ?/
Grzegorz Jaśkiewicz wrote: > what difference does the (0) make than ? is timestamp() a function than ?/ > The (0) is setting the precision. Telling it to store 0 places for the fractional second. Much like setting scale and precision with numeric(6,2) Scott