Thread: Function to convert bigint to date
Hi all,
For my application we are storing the date information by casting it into BIGINT using the following
extract('epoch' from current_timestamp)::bigint
But how do I convert the BIGINT to the date format 'dd mm yyyy' a function similar to to_date(text,text)
Is there any function to do this conversion.
Thanks in advance
With Best Regards,
Pradeep Kumar P.J
* Pradeepkumar, Pyatalo (IE10) <Pradeepkumar.Pyatalo@honeywell.com> [2004-10-21 07:47:21 -0700]: > But how do I convert the BIGINT to the date format 'dd mm yyyy' a > function similar to to_date(text,text) This reply from Richard Huxton should do the trick: http://archives.postgresql.org/pgsql-sql/2002-07/msg00397.php -- Steven Klassen - Lead Programmer Command Prompt, Inc. - http://www.commandprompt.com/ PostgreSQL Replication & Support Services, (503) 667-4564
Hi, But from the result I need to display only the date and not the time. Should I use extract() function to get the day, month and year seperately and then form the date format, or is there any other way to extract only the date part from the timestamp. Regs, Pradeep -----Original Message----- From: pgsql-novice-owner@postgresql.org [mailto:pgsql-novice-owner@postgresql.org] On Behalf Of Steven Klassen Sent: Thursday, October 21, 2004 9:48 PM To: Pradeepkumar, Pyatalo (IE10) Cc: pgsql-novice@postgresql.org Subject: Re: [NOVICE] Function to convert bigint to date * Pradeepkumar, Pyatalo (IE10) <Pradeepkumar.Pyatalo@honeywell.com> [2004-10-21 07:47:21 -0700]: > But how do I convert the BIGINT to the date format 'dd mm yyyy' a > function similar to to_date(text,text) This reply from Richard Huxton should do the trick: http://archives.postgresql.org/pgsql-sql/2002-07/msg00397.php -- Steven Klassen - Lead Programmer Command Prompt, Inc. - http://www.commandprompt.com/ PostgreSQL Replication & Support Services, (503) 667-4564 ---------------------------(end of broadcast)--------------------------- TIP 7: don't forget to increase your free space map settings
Hi, But from the result I need to display only the date and not the time. Should I use extract() function to get the day, month and year seperately and then form the date format, or is there any other way to extract only the date part from the timestamp. Regs, Pradeep -----Original Message----- From: pgsql-novice-owner@postgresql.org [mailto:pgsql-novice-owner@postgresql.org] On Behalf Of Steven Klassen Sent: Thursday, October 21, 2004 9:48 PM To: Pradeepkumar, Pyatalo (IE10) Cc: pgsql-novice@postgresql.org Subject: Re: [NOVICE] Function to convert bigint to date * Pradeepkumar, Pyatalo (IE10) <Pradeepkumar.Pyatalo@honeywell.com> [2004-10-21 07:47:21 -0700]: > But how do I convert the BIGINT to the date format 'dd mm yyyy' a > function similar to to_date(text,text) This reply from Richard Huxton should do the trick: http://archives.postgresql.org/pgsql-sql/2002-07/msg00397.php -- Steven Klassen - Lead Programmer Command Prompt, Inc. - http://www.commandprompt.com/ PostgreSQL Replication & Support Services, (503) 667-4564 ---------------------------(end of broadcast)--------------------------- TIP 7: don't forget to increase your free space map settings
* Pradeepkumar, Pyatalo (IE10) <Pradeepkumar.Pyatalo@honeywell.com> [2004-10-25 02:23:19 -0700]:
> But from the result I need to display only the date and not the
> time. Should I use extract() function to get the day, month and year
> seperately and then form the date format, or is there any other way
> to extract only the date part from the timestamp.
You can cast a timestamp to a date. You really need to give these
things a try yourself. You'd be surprised what you can figure out
through trial and error.
xinu=> select now()::timestamp;
now
----------------------------
2004-10-25 23:45:38.939148
(1 row)
xinu=> select (now()::timestamp)::date;
now
------------
2004-10-25
(1 row)
--
Steven Klassen - Lead Programmer
Command Prompt, Inc. - http://www.commandprompt.com/
PostgreSQL Replication & Support Services, (503) 667-4564