updating years in PostgreSQL

[INTLuis]

I need to update a date field (the date, not the hour) in postgreSQL to another year.

In Access it's so simple as :

UPDATE historiales SET historiales.hora = CDate(Format([fecha], "dd/mm/yyyy&quot + " " + Format([hora], "hh:mm:ss&quot) WHERE (((Year([hora])) = 1899));

Where [fecha] and [hora] are 'timestamptz' type. In the field [hora] I must keep the same hour, but changing the date with the value contained in the field [fecha].

How could I do this in postgreSQL?

Thank you very much!

 

[Leland123]

Hi INTLuis,

Dates are inserted into a postgres table as strings. The positions of the substrings are used by postgres to perform time functions.

I don't have any experience with PHP, but using perl I could place the content of a record into an array. I could then place the first four characters of the date array (e.g. the year part of the date) into a variable and add 1 to this variable. I could then combine the new variable with the sub-string portion of the date array, (e.g. that part of the date that is not to be changed), to construct a new string variable. Then I would update the table record date field with the new string.

This can also be done using foxpro and string manipulation.

Also, you can find some function examples of date manipulation at the below link, but I think it would be better to use php to change the date.

http://www.brasileiro.net/postgres/cookbook/

Leland F. Jackson, CPA
Software - Master (TM)

https://www.smvfp.com

Nothing Runs Like the Fox

 

[rycamor]

This can be done inside PostgreSQL, with a query. No need to rely on outside programming.

PostgreSQL's date manipulation functions are every bit as capable as Access, if not more. The syntax might be a little different, but it is fairly ANSI SQL-92 compliant. The main method for mainpulating the format of a date is the to_char(timestamp, '[options]') function, which casts a data as a string, and then allows for standard date formatting options. (in other words replace [options] above with "dd/mm/yyyy", or whatever, from this page in the PostgreSQL manual:

http://www.postgresql.org/idocs/index.php?functions-formatting.html)

You can extract any part of a date field, such as the year, with the EXTRACT function, such as:

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

(

http://www.postgresql.org/idocs/index.php?functions-datetime.html#FUNCTIONS-DATETIME-EXTRACT)

PostgreSQL also has some very capable ways of adding and subracting time from a certain date, using the INTERVAL datatypes and functions. (

http://www.postgresql.org/idocs/index.php?functions-datetime.html)

INTERVAL is both a datatype and a timestamp-modifying function. Thus you can also store an interval in a column. This datatype is the closest thing to AI I have seen in any database system. It can be done in a completely human-readable format, such as '4 months 3 days 2 hours 3 seconds', '2 weeks 10:43:23.234234' etc... It allows for anything from millenia to microseconds (NOT milliseconds...microseconds!)).

In short, PostgreSQL's date-manipulation features are probably it's best-kept secret, which many would do well to discover. -------------------------------------------

Big Brother: "War is Peace" -- Big Business: "Trust is Suspicion"
(

http://www.cl.cam.ac.uk/~rja14/tcpa-faq.html)