Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

updating years in PostgreSQL

updating years in PostgreSQL

updating years in PostgreSQL

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") + " " + Format([hora], "hh:mm:ss")) 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!

RE: updating years in PostgreSQL

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.


Leland F. Jackson, CPA
Software - Master (TM)
Nothing Runs Like the Fox

RE: updating years in PostgreSQL

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:

Result: 2001


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"

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close