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>:
Show quoted text
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
what difference does the (0) make than ? is timestamp() a function than ?/
=?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
