How to Use the Postgres Select Now Function

Written by Data Pilot
February 17, 2020
  • Redis
Have a Database Problem? Speak with an Expert for Free
Get Started >>

Introduction

This tutorial will cover the PostgreSQL GETSET and NOW() functions. A Redis get set data type is an unordered collection of unique and automatically specified string values. The SELECT statement is used to select certain specified data from the database and the NOW() function is used to obtain the current date and time based on the database server’s current time-zone setting.

Prerequisites

  • A Redis server and cli must be properly installed and working on the local system. Execute the redis-server --version command and the redis-cli --version command to verify both are installed and to obtain the version number of each program.

  • Both of the above programs must be started on the local system device. Execute the sudo systemctl status redis command for a Linux distribution operating system that uses the system software to display the current status of the Redis database.

  • A good working knowledge of PostgreSQL is needed to follow the instruction in this tutorial and use the Redis get set data type.

Execute the following command to start the Redis database:

1
systemctl start redis

Now execute the following command to automatically enable Redis after shutting down the machine:

1
sudo systemctl enable Redis

The Redis GETSET data type

A Redis GETSET is an unordered collection of unique strings and automatically specified string values in a Redis key. As the following section will explain the Postgres select now function, the PostgreSQL syntax must be installed on the computer being used.

PostgreSQL now function examples

The following example explains how to obtain the current time setting of the local machine:

1
2
3
4
5
6
7
8
9
SELECT NOW();

now

-------------------------------

2020-02-13 10:25:26.146189+08

(1 ROW)

Now the system’s current date, time and the time zone settings must be obtained. Note that because the NOW() function only returns the system’s current date and time setting, changing the time zone will also change the return output.

The following example shows the time zone is set to ‘America/New_York’:

1
SET TIMEZONE = 'America/New_York';

If the current date and time are selected again using the NOW() function, the output will be as follows:

1
2
3
4
5
6
7
8
9
SELECT NOW();

now

-------------------------------

2020-02-02 20:16:04.903474-05

(1 ROW)

Note the returned value of the NOW() function has been changed to the new time zone.

Select now function with type cast examples

To use the GETSET function, execute the following command:

1
127.0.0.1:6379> GETSET key value

Here is an example of what the output should look like:

1
2
3
4
5
6
7
127.0.0.1:6379> SET test abc

OK

127.0.0.1:6379> GET test

"abc"

Note the key name test, that has a value of abc, was created with the simple SET and GET commands.

If the GETSET command is used, the following will result:

1
2
3
127.0.0.1:6379> GETSET test "cde"

"abc"

Note that this result occurred because the current date and time was input without the time zone.

Now examine the following two example queries.

Example query one:

1
2
3
4
5
6
7
SELECT CAST( NOW() AS TIMESTAMP );

now

----------------------------

2020-02-02 20:36:17.400539

Example query two::

1
2
3
4
5
6
7
SELECT NOW()::TIMESTAMP;

now

----------------------------

2020-02-02 20:36:17.400539

Notice that there is no time zone in the returned value of the NOW() function commands.

NOTE: The two above queries, that returned the date and time without the time zone, already have a key named test with a value of abc.

The GETSET command or data type shows it returns the old key string value.

To confirm the new value was added inside the key, use either of the two valid syntax for type casts. To get the current date using only the NOW() function, again execute the command GET followed by the key name as shown here:

1
2
3
127.0.0.1:6379> GET test

"cde"

Use the Getset command to a new key

If a new key name and value is inserted in Redis using the GETSET command, a (nil) output will be returned:

1
2
3
127.0.0.1:6379> GET test1

"efg"

NOTE: This occurs because of the syntax of the Redis GETSET command.

If a new key name is entered with the GETSET command, it will not return a value because a new value was input without an old value.

However, using the command GET command will return the following:

1
2
3
127.0.0.1:6379> GET test1

"efg"

Using Getset with incr data type

Executing the following query will return just the date:

1
2
3
4
5
6
7
8
9
SELECT NOW()::DATE;

now

------------

2020-02-02

(1 ROW)

Or, execute the following the NOW() function query to obtain only the current time:

1
2
3
4
5
6
7
8
9
SELECT NOW()::TIME;

now

-----------------

21:07:10.730233

(1 ROW)

Select now function with interval examples

The NOW() function will only work for obtaining the current date and time. The following example demonstrates how to get the time an hour later the current time:

1
2
3
4
5
6
7
8
9
SELECT ( NOW() + INTERVAL ' 1 hour ' ) AS one_hour_later;

one_hour_later

-------------------------------

2020-02-02 22:02:15.609838-05

(1 ROW)

Note the GET command can also be used in the INCR data type by creating an integer value that increases the date and time by one a week from the present:

1
2
3
4
5
6
7
8
9
10
11
127.0.0.1:6379> SET count 10

OK

127.0.0.1:6379> INCR count

(integer) 11

127.0.0.1:6379> GETSET count "21"

"11"

NOTE: In this example the value for the count returns the previous value of the integer that was just changed.

Now execute the following command to get the string value of the GETSET:

1
2
3
127.0.0.1:6379> GET count

"21"

Here the count becomes a string when it is used with a GETSET command.

sql

SELECT ( NOW() + INTERVAL ‘ 1 week ‘ ) AS time_next_week;

time_next_week

2020-02-09 21:05:59.673858-05

(1 row)

1
2
3
4
5
6
7
8
9
10
11
12
13
For example, use the minus (-) operator as follows to return a time five hours previous:

```sql

SELECT ( NOW() - INTERVAL ' 5 hours ' ) AS five_hours_ago;

five_hours_ago

-------------------------------

2020-02-02 16:15:36.810765-05

(1 row)

Conclusion

This tutorial explained how to use the Redis get set type and the PostgreSQL NOW function. The article specifically explained and provide type-cast examples on how to execute the PostgreSQL NOW function and then covered the proper use of the GETSET command and how to use the command with the INCR data type. The tutorial then explained the SELECT NOW function and provided interval examples. Remember that if a new key name is entered with the GETSET command, it will not return a value because a new value was input without an existing old value.

Rate 1 Star2 Stars3 Stars4 Stars5 Stars

Pilot the ObjectRocket Platform Free!

Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.

Get Started
Related Topics:
Additional Information:

Keep in the know!

Subscribe to our emails and we’ll let you know what’s going on at ObjectRocket. We hate spam and make it easy to unsubscribe.