Hello,
I know that PostgreSQL, like most database management systems, has a
function
call called NOW() that returns the current date. Is there a way to
return a datein PostgreSQL such that the output is in ISO 8601 format
(Unix 'date -I' format)but such that the date is not "today"'s date
but the date two days ago or five
days ahead of now? I have tried something like NOW() + 5 but that did
not work
(because the data types are incompatible, and SELECT NOW() +
'0000-00-01' does
not work either. I get the error:
ERROR: Bad interval external representation '0000-00-01'
Thanks,
Neil 6 10309
On Sat, Oct 25, 2003 at 09:35:35PM -0700, Neil Zanella wrote: Hello,
I know that PostgreSQL, like most database management systems, has a function call called NOW() that returns the current date. Is there a way to return a datein PostgreSQL such that the output is in ISO 8601 format (Unix 'date -I' format)but such that the date is not "today"'s date but the date two days ago or five days ahead of now?
Certainly. Try the following:
SELECT now() + 5 * '1 day'::interval;
Or, more verbose,
SELECT now() + 5 * CAST('1 day' AS interval);
You can of course do
SELECT now() + CAST('5 day' AS interval);
But the two previous examples can be more easily constructed in an SQL o
PL/pgSQL function.
For the date -I format you can use something like
SELECT to_char(now() + 5 * '1 day'::interval, 'YYYY-MM-DD');
--
Alvaro Herrera (<alvherre[a]dcc.uchile.cl>)
"La tristeza es un muro entre dos jardines" (Khalil Gibran)
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives? http://archives.postgresql.org
On Sat, 25 Oct 2003, Neil Zanella wrote: Hello,
I know that PostgreSQL, like most database management systems, has a function call called NOW() that returns the current date. Is there a way to return a datein PostgreSQL such that the output is in ISO 8601 format (Unix 'date -I' format)but such that the date is not "today"'s date but the date two days ago or five days ahead of now? I have tried something like NOW() + 5 but that did not work (because the data types are incompatible, and SELECT NOW() + '0000-00-01' does not work either. I get the error:
If you want a date, I'd suggest something like
CURRENT_DATE+5
The reason this works while, now()+5 doesn't is that now() doesn't return
a date, but a timestamp type (including time).
If you want time information, then probably
CURRENT_TIMESTA MP + INTERVAL '5 days'
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postg resql.org so that your
message can get through to the mailing list cleanly
Postgres has a type called "interval" to deal with these kinds of
situations. As you might expect, an interval is a duration of time, as
opposed to a fixed time ordinate like date or timestamp. You can add
and subtract interval values from dates and timestamps. Intervals need
to be specified as strings and then cast to interval. You can do this
in two ways:
interval '5 days'
'5 days'::interval
So to get "five days ago", you would use
now() - interval '5 days'
Similarly, to get 40 minutes into the future
now() + interval '40 minutes'
The resultant date or timestamp value can then be expressed in whatever
format you please by using to_char()
BJ
Neil Zanella wrote: Hello,
I know that PostgreSQL, like most database management systems, has a function call called NOW() that returns the current date. Is there a way to return a datein PostgreSQL such that the output is in ISO 8601 format (Unix 'date -I' format)but such that the date is not "today"'s date but the date two days ago or five days ahead of now? I have tried something like NOW() + 5 but that did not work (because the data types are incompatible, and SELECT NOW() + '0000-00-01' does not work either. I get the error:
ERROR: Bad interval external representation '0000-00-01'
Thanks,
Neil
---------------------------(end of broadcast)--------------------------- TIP 8: explain analyze is your friend
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postg resql.org so that your
message can get through to the mailing list cleanly
BlakJak <bl*****@blakja k.sytes.net> writes: So to get "five days ago", you would use now() - interval '5 days'
Actually, given that the OP seems to only want a date result and not a
time-of-day, I'd suggest something like
current_date - 5
The date-plus-integer and date-minus-integer operators do exactly what
I think is being asked for. timestamp-minus-interval does computations
including fractional days, which will just confuse matters
.... especially near DST transition days. For instance, right now I get
regression=# select now();
now
-------------------------------
2003-10-27 01:45:14.458268-05
(1 row)
regression=# select now() - interval '5 days';
?column?
------------------------------
2003-10-22 02:45:20.22788-04
(1 row)
which is correct in one sense but is surely going to confuse some
people.
regards, tom lane
---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend al******@dcc.uc hile.cl (Alvaro Herrera) wrote in message Certainly. Try the following: SELECT now() + 5 * '1 day'::interval;
Or, more verbose, SELECT now() + 5 * CAST('1 day' AS interval);
You can of course do SELECT now() + CAST('5 day' AS interval);
But the two previous examples can be more easily constructed in an SQL or PL/pgSQL function.
Perhaps I should get myself a copy of the relevant parts of the SQL 99 standard.
How would you do the above in standard SQL?
For the date -I format you can use something like SELECT to_char(now() + 5 * '1 day'::interval, 'YYYY-MM-DD');
I believe Oracle also has a to_char() function. Is this to_char() function
part of standard SQL or is it just a coincidence that both DBMSs support
such a function call? I wonder whether the PostgreSQL to_char()
function is compatible with the Oracle one.
Thanks,
Neil
On Mon, Oct 27, 2003 at 01:40:53AM -0800, Neil Zanella wrote: al******@dcc.uc hile.cl (Alvaro Herrera) wrote in message
You can of course do SELECT now() + CAST('5 day' AS interval);
Perhaps I should get myself a copy of the relevant parts of the SQL 99 standard. How would you do the above in standard SQL?
I think one standard way of doing the above would be
SELECT CURRENT_TIMESTA MP + CAST('5 day' AS interval);
Or, as pointed out by Tom Lane and someone else, if you don't need the
time part,
SELECT CURRENT_DATE + 5; For the date -I format you can use something like SELECT to_char(now() + 5 * '1 day'::interval, 'YYYY-MM-DD');
I believe Oracle also has a to_char() function. Is this to_char() function part of standard SQL or is it just a coincidence that both DBMSs support such a function call? I wonder whether the PostgreSQL to_char() function is compatible with the Oracle one.
AFAIK the main motivation to create the to_char() function in the first
place was in fact Oracle compatibility. If you want to do such a thing
in a standard manner, you should probably do
SELECT EXTRACT(year FROM a) || '-' ||
EXTRACT(month FROM a) || '-' ||
EXTRACT(day FROM a)
FROM (SELECT CURRENT_DATE + 5 AS a) AS foo;
--
Alvaro Herrera (<alvherre[a]dcc.uchile.cl>)
"I would rather have GNU than GNOT." (ccchips, lwn.net/Articles/37595/)
---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postg resql.org This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
by: Brian |
last post by:
Here's the example:
You have a world writable file called lastquote.cfg on your server with a
one or two digit number in it. When you run the script below, it reads the
value, increments the value by one and then rewrites it into the file.
<?php
$fp = fopen("lastquote.cfg", "r");
$data = fread($fp, 10);
fclose($fp);
|
by: Mike Brearley |
last post by:
I have a counter (alright one I found on asp101.com) that checks for a
session variable to prevent the counter from incrmenting if a user refreshes
the page or returns to the page during the same session. The problem is, it
also prevents other page counters from incrementing. Here's the code for
this part:
' Increment the count
if Session("iCount") <> iCount then
iCount = iCount + 1
Session("iCount") = iCount
|
by: J. Campbell |
last post by:
Hi everyone. I'm sure that this is common knowledge for many/most
here. However, it was rather illuminating for me (someone learning
c++) and I thought it might be helpful for someone else. I'm sure I'll
get scolded for an OT post, but I think issues of this type should be
of interest to programmers until they get a handle on them.
I was reading some old threads about the relative advantages of using
++i vs i++ (I won't rehash the many...
|
by: brian |
last post by:
Hi, before coming to .NET, I utilized regular expressions mostly in JScript /
JavaScript and also in my favorite text editor: TextPad (www.textpad.com)
I don't know about JScript/JavaScript, but in TextPad's implementation of
Regular Expressions, you can do a replacement expression like this: \i
For every single non-overlapping match, it places an incrementing number
starting with zero. Thus, if you searched for ^ (beginning of line...
|
by: pozz |
last post by:
Hi all,
I need to write a simple incrementing/decrementing function like this:
unsigned char
change( unsigned char x, unsigned char min, unsigned char max, signed char d);
x is the value to increase/decrease
min is the minimum value that x can assume
max is the maximum value that x can assume
| |
by: Shannon Jacobs |
last post by:
The OL tag still allows for a START value, but that is now deprecated.
I've found sound references that suggest the proper technique now is
to control it with a style for the OL in quetion, but I haven't been
able to find the proper reference. What I actually want is an ordered
list that counts down to one. Are negative increments even possible?
Doesn't seem like a ridiculous idea in the real world, but...
(Yes, I also searched these...
|
by: jesmi |
last post by:
hi
i got problem in inserting the date into the database. my requirement is that when i choose a date ie from :2007-01-01 & to :2007-12-01 then all the dates starting from 2007-02-01 upto 2007-12-01 should be inserted. while inserting year,month and day should be incremented.i tried a lot and my code only increments the month. Following is my code:
public void save(String eventDt,String toDt) throws Exception{
Connection con =...
|
by: Bill Cunningham |
last post by:
I don't know if I'll need pointers for this or not. I wants numbers
10^16. Like a credit card 16 digits of possible 10 numbers, so I guess that
would be 10^16. So I have
int num ;
These are of course declared and not initialized. Now I want to initialize
them all with '\0'. That I'm guessing would involve while ( --). Or maybe
for. Would pointers be involved in this?
With this excercise I would learn working with multi-dimensional
|
by: Richard |
last post by:
I'm still battling with this causing UDB:
while(e-- s);
if s points to the start of a string and e becomes less than s then e is
not really pointing to defined char. Fine.
But UDB?
Yes, e has an UDV (undefined value) but would this really cause a
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
| |
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |