IP address and the timestamp storage

Question

I'm trying to store IP address and a PHP session id to uniquely identify a user, to manage a user queue to control a (one) hardware device through internet with a token. When a user has the token, has permission to control the device for 2 minutes. Then I also need a timestamp, the time on which the user asked for the token.

What is the correct field type for the IP address and the timestamp in SQLite? I need an easy way to retrieve a queue from the database matching a "text" cookie session id and an IP, using timestamp to order and filter. Should I use an integer or text field? Are there functions to work with those types?

Answer 1

SQLite is weak type, but supports type affinity. At all SQLite only support a small range of "column types" ("type affinities")

INTEGER
REAL
NUMERIC
TEXT
BLOB
NONE

However, in your case you can choose: You can store a timestamp as UNIX-timestamp in INTEGER, or as datetime formated string in TEXT. See the section "1.2 Date and Time Datatype" in the document provided by the link above. There are Date And Time Functions to help you handle this kind of data.

The IP can be stored as INTEGER after converting it into one: ip2long(). Or you store it as TEXT too. I suggest to use the former one.

Update: If you choose to use INTEGER, you will be limited to storing IPv4 addresses only, because SQLite can only store 64 bit integers, whereas IPv6 are 128 bit. However, because ip2long only works with IPv4 the range is not the only issue.